How to use a lookup table with threshold values in Alteryx

Question

Hi,

I have a very large banding lookup table which assigns a 'Band number' for a range of continuous values. I want to assign the correct band number to each customer based on the field 'spend value' which is in my primary dataset.

There are 390 bands and the range of values which make up a band are not equal (sometimes the numeric range is 100s and for other bands it's 1000s).

I would use a 'Join' with multiple matching criteria but I cannot in this case as I need to specify a range of values and say:

If [spend value]>=0 && [spend value]<100 Then [Band]=1

If I use the formula tool then I need to write 390 conditions in my statement which I will do if there isn't an easier way.

Can anyone suggest how to do this elegantly using my Banding lookup table (which might also change overtime).

Many thanks in advance for all suggestions - I'm sorry I cannot share an example workflow as I don't know how to efficiently go about this task.

Nicola

Luke_C · Answer

Hi @Nicola1

Here's my pass at this. There are definitely other ways to go about doing it so if you run into performance issues on your full dataset let us know

New Workflow9.yxmd

Nicola1 · Answer

Hi Luke,

Please see the attached example data. The Customer data is the file which I'd like to assign the correct band to based on the customer's spend by car brand and the lookup table.

The real data has 10,000 customer records and 390 possible bands by car brand and car spend.

The spend values in the lookup file denote the minimum spend and a customer sits within that band if they spend the denoted value as a minimum up to the value denoted in the next band up.

Thanks for any thoughts that you have on how to solve this.

KR,

Nicola

Alteryx_lookup.xlsx

Luke_C · Answer

Can you share some sample data? My initial thought is a dynamic replace tool might help with this.