Desktop productivity for business analysts and programmers

Table Joins in Guide 5.1

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

Table Joins in Guide 5.1

Database desgn has a lookup table that I need to join against to translate values from the primary data tables.  So in SQL I Would do this by:

 

Select b.value_desc as 'Status',

           a.variable2,

           a.variable3

 

From variable_table a

 

LEFT JOIN lookup b

on a.lookup_status= b.column_value and b.column_name = 'lookup_status'

 

Lookup Table

column_valuecolumn_namevalue_desc
1lookup_statusOpen
2lookup_statusClosed
3lookup_statusPending

 

So if the value was 1 in the data table I would get back Open,when  2 Closed etc. 

 

I have a bunch of work completed already in project mode and I know there is a way to do this in Guide using the query builder tables and joins in combination with an advanced expression, could not find an example in the forum.

 

Added Complexity

Within the data table there may 3 or 4 lookup values, can I do multiple joins to the lookup table for each lookup value?  In SQL I'd have to rejoin to the lookup table for each variable against which I wanted value_desc returned.

 

Thanks   


Accepted Solutions
Solution
‎06-20-2016 10:40 AM
Contributor
Posts: 20

Re: Table Joins in Guide 5.1

Per usual the answer is pretty straight forward

 

QueryBuilder> Join Tables> Join Properties> Edit> code for the value you want returned

 

May vary slightly if your Options are not set to automatically choose the join variable  

View solution in original post


All Replies
Trusted Advisor
Posts: 2,114

Re: Table Joins in Guide 5.1

Although you can do the lookups in SQL, do you really want the overhead of carrying those labels as part of a new data set?  Have you considered using PROC FORMAT to label the results?  You can use the contents of the lookup table to generate formats and then just link them when needed.  There is an example of doing that in the PROC FORMAT documentation.  It is also often quicker than the SQL-only aproach.

Contributor
Posts: 20

Re: Table Joins in Guide 5.1

Proc Format is a possible solution and I have done that if and when the data table values are stable and I don't need to complete too many lookup joins. 

 

For this data set some of the data values will be changing over time so I really do need to join to the tables to ensure the final output will always reflect the updated values. 

 

Also, have been asked to build out in project mode so the less technical can gain a better perspective on the data aggregation through a conversation using the project window as the visualization tool.

Super User
Posts: 11,114

Re: Table Joins in Guide 5.1


RickyS wrote:

Proc Format is a possible solution and I have done that if and when the data table values are stable and I don't need to complete too many lookup joins. 

 

For this data set some of the data values will be changing over time so I really do need to join to the tables to ensure the final output will always reflect the updated values. 

 

Also, have been asked to build out in project mode so the less technical can gain a better perspective on the data aggregation through a conversation using the project window as the visualization tool.


I have a project that adds values at least monthly. I find it worth while to periodically examine the data and update formats, several are multilabel formats, because it makes the report writing later much easier. If these values change over time, who updates the lookup table(s) you would be using? An update to that table could trigger an update to the format.

Just a thought.

Occasional Contributor
Posts: 6

Re: Table Joins in Guide 5.1

Need some clarification regarding the variables in each table.The logic so far is:  when  a.lookup_status=1 and a.lookup_status=b.column_value and b.column_name='lookup_status' then 'Status'=b.value_description.

 

what do you mean when writing Within the data table there may be 3 or 4 lookup values?  Which table a/b is the data table?  What variable in which table may have 3 or 4 look values.  Are these look values evaluated against another table.variable?

Contributor
Posts: 20

Re: Table Joins in Guide 5.1

The database has around 100 primary data tables and 1 lookup table, each of the 100 data tables may have 20 - 30 variables of which  4 - 5 require translating by joining to the lookup table.

 

So you can use a Case statement in SQL, Proc Format in SAS to translate or Table join on the variable Name to the Lookup table to extract the translated value which is 'value_desc'.  Pros and cons to each approach depending upon what you are doing.

 

Simplified Perspective of a Data Table

Variable Name
table_id
primary_key
secondary_id
lookup_status
lookup_type_of_client
lookup_client_company_structure
Lookup_client_business_volume
other_variable_1
other_variable_2
other_variable_3
other_variable_4
other_variable_5
other_variable_6
other_variable_7
other_variable_8
other_variable_9
other_variable_10
other_variable_11
other_variable_12
other_variable_13
other_variable_14
other_variable_15
other_variable_16
other_variable_17
other_variable_18

 

 

 

 

Contributor
Posts: 20

Re: Table Joins in Guide 5.1

put a ticket in with tech support yesterday

Solution
‎06-20-2016 10:40 AM
Contributor
Posts: 20

Re: Table Joins in Guide 5.1

Per usual the answer is pretty straight forward

 

QueryBuilder> Join Tables> Join Properties> Edit> code for the value you want returned

 

May vary slightly if your Options are not set to automatically choose the join variable  

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 463 views
  • 0 likes
  • 4 in conversation