Lookup and find value in one table based on input from another table.

[kapoorp]

I have one .yxdb file which has a column say Age (numeric value). I am trying to have a mapping done for this age with data from another .yxdb file while has details for Min_Age, Max_Age, Entry, Level1, Level2, Leve3, Level4. Min_age and Max_age provide an age range,  Entry, Level1, Level2, Leve3, Level4 are the designation category levels1.

example  of data- T1

Name	Age	Desgination
A	24	entry
B	21	L1
C	26	L2
D	27	L2
E	32

T2:

Min_age	Max_age	Entry	L1	L2	L3	L4
20	22	500	600	650	696	720
22	23	500	600	656	700	720
23	24	520	620	680	710	745
24	25	524	625	680	710	745
25	30	530	650	690	720	750

so the task I am trying is that it will take age from T1, check it as to which range it belongs to from T2 - min_age & max_age and then based on designation, go to that particular cell that corresponds to age range and designation.

I am considering the case as age>=20 and less than 22 and subsequent check could be age =22, age=23 and so on.

So, for A-compensation would come as 520. If else check goes too long so not able to find what could be the best way to achieve this in alteryx.

[RolandSchubert · Accepted solution]

Hi @kapoorp ,

I think you can use a Transpose tool as the first step. Thereby you would create a table with Age_Min, Age_Max, Name (=Designation) and Value (= compensation). Then you can Join by T2 [Name] = T1 [Designation] . You need to add a Formula tool using a formula like

IF [Age] >= [Min_Age] AND [Age] < [Max_Age] THEN
[Value]
ELSE

Null()
ENDIF

and remove the "not matching" rows by a filter tool. See attached workflow. Do you think, that is a solution?

Best regards

Roland

[kapoorp]

Thanks, Roland. I was able to use your suggestion in my scenario and it works well.