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

Question

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

NameAgeDesginationA24entryB21L1C26L2D27L2E32

T2:

Min_ageMax_ageEntryL1L2L3L420225006006506967202223500600656700720232452062068071074524255246256807107452530530650690720750

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 Answer

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