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:
Solved! Go to Solution.
Ah, thank you @JosephSerpis. This works absolutely fine. I can't believe I couldn't think of this! 🙂
@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
Agree with you @DiganP