Vlookup lies between

Question

Hi All,

In my data I have a days column which ranges from 0 - 800, based on this column I want to add a age of the bill column which will give me like if its 0 to 41  days the it would be  6 weeks or less and further. In excel i did this by vlookup. In alteryx sould I give this as manual input or an if formula.

As I tried the manual input it only shows for 0 - 6 weeks, for 42 - 6weeks.

06 weeks or less426 weeks - 2 years7302 years +

Thank you

Regards

Rohini

estherb47 · Answer

Hi @rohini

Great idea, @Kenda   You could also use an append tool to append all of the possible lookups, and a filter to find where they lie in the lookup table.

and finally, you could use a generate rows tool to find all of inbetween values, and a join tool to match.

let me know if that helps.

cheers!

esther

Kenda · Answer

Hi @rohini

I would suggest creating a new field with a Formula tool with the following expression:

iif([Days]<42, "6 weeks or less", 
     iif([Days]<720, "6 weeks - 2 years", "2 years +"))

This first checks if the Days field is less than 42. If so, the new field will say 6 weeks or less. If not, it will next check if the Days field is less than 720. If so, the new field will say 6 weeks - 2 years. If neither of those conditions are true, the new field will say 2 years +.

Hope this helps!