Conditional Join Query

[agrawaluk]

Hi,

I have two tables as below

Table 1:

Table 2:

I want to make a join for Employee Group and R_Table, but only if the YOS match PTO YRS. BUT, there is another condition:

IF [YOS] < 10 THEN [AMT_PCT]
ELSEIF [YOS] <20 OR [YOS] > 9 THEN [AMT_PCT]
ELSEIF [YOS] > 20 THEN [AMT_PCT]
else 0 endif

Basically PTO YRS has buckets: If YOS = 1 to 9 then PTO YRS = 0, If YOS = 10 - 19 then PTO YRS = 10, If YOS = 20+ then PTO YRS = 20 for the particular R_Table. 

I am looking to make a join that would give me this result: 

[JosephSerpis · Accepted solution]

Hi @agrawaluk I mocked up an example workflow let me know what you think?

[agrawaluk]

Ah, thank you @JosephSerpis. This works absolutely fine. I can't believe I couldn't think of this! 🙂

[DiganP]

@agrawaluk I like @JosephSerpis solution. The only change I would make would be to make the formula in the formula tool more precise.

IF ([YOS]>=1 and [YOS]<=9) THEN 0 ELSEIF ([YOS]>=10 and [YOS]<=19)THEN 10 ELSEIF ([YOS]>=20) THEN 20 ELSE 0 ENDIF

[agrawaluk]

Agree with you @DiganP