Desktop productivity for business analysts and programmers

Join Tables on Variables using a Begin With or Like Function

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Join Tables on Variables using a Begin With or Like Function

I have a set of diagnosis codes that were provided with an X variable, where the X represents some numeric value.  Ex. 410.0x.  The x can be a 1 or 2.  I want to join two tables 1) has the 410.0x and the 2) has the 410.01 and 410.02.  How do I link the tables on the variable that allows for a Begins with '410.0' and returns the 410.01 and 410.02


Accepted Solutions
Solution
‎10-13-2016 02:45 PM
Grand Advisor
Posts: 17,428

Re: Join Tables on Variables using a Begin With or Like Function

When you join you can include functions in the join. So substr the variable if you want only the first 5 digits to match

 

 

View solution in original post


All Replies
Occasional Contributor
Posts: 11

Re: Join Tables on Variables using a Begin With or Like Function

Do you have common variable for merging two datasets or tables other than Diagnostic codes?

New Contributor
Posts: 2

Re: Join Tables on Variables using a Begin With or Like Function

No. The second table is a dimension tables to serve as a lookup.

Solution
‎10-13-2016 02:45 PM
Grand Advisor
Posts: 17,428

Re: Join Tables on Variables using a Begin With or Like Function

When you join you can include functions in the join. So substr the variable if you want only the first 5 digits to match

 

 

Respected Advisor
Posts: 4,609

Re: Join Tables on Variables using a Begin With or Like Function

You could join on the condition:

 

translate(code1, "1", "x") = code2 OR translate(code1, "2", "x") = code2

 

 

PG
☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 323 views
  • 1 like
  • 4 in conversation