Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Conditional Join Query

agrawaluk
8 - Asteroid

Hi,

 

I have two tables as below

Table 1:

 

agrawaluk_1-1573497928269.png

 

Table 2:

agrawaluk_2-1573497944604.png

 

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: 

 

agrawaluk_3-1573498382788.pngagrawaluk_4-1573498382790.png

4 REPLIES 4
JosephSerpis
17 - Castor
17 - Castor

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

 

Join_111119.PNG

agrawaluk
8 - Asteroid

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

DiganP
Alteryx Alumni (Retired)

@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

 

 

Digan
Alteryx
agrawaluk
8 - Asteroid

Agree with you @DiganP 

Labels