Multiple Join Based on Column Value

Question

Hello ,

I am needing help in doing a join based on multiple field depending on column values.
I have two data sets - Table 1 and Table 2.
The join in SQL is as below followed by a where clause:

LEFT JOIN TABLE 1 and TABLE 2

ON T1.LOAN_NUMBER = T2.LOAN_NUMBER AND T1.BANKRUPTCY_YN = 'Y' AND T2.RN = 1

WHERE T1.RN = 1

The T1.BANKRUPTCY_YN = 'Y' AND T2.RN = 1 is the part in the join i am having trouble with. I thought of creating a custom  filed for a join key like 
IF T1.LOAN_NUMBER = T2.LOAN_NUMBER AND T1.BANKRUPTCY_YN = 'Y' AND T2.RN = 1 THEN 1 Else 0 END IF and then use a Filter tool with the join key = 1. 
But that doesn't seem to work.

Any help would be appreciated on this.

Thank you.

Table 1.xlsx

Table 2.xlsx

R726319 · Answer

Hi T_Willins, i previously tried this same approach but unfortunately it didn't give the expected output.

T_Willins · Answer

Hi @R726319,

See if this works for you:

Multiple Criteria Join.yxmd

T_Willins · Answer

Hi @R726319 .  I'm looking at your data, but you should be able to filter your data streams before joining on T1.BANKRUPTCY_YN = 'Y"  and T2.RN = 1, then join the remaining data similar to below (not workflow based on your actual data)

I'll look at your data and post a more complete reply.