Using Look Up Tables Instead of Long If/Then Statements????

[jbh1128d1]

I'm wondering how I can use lookup tables to assign values to be multiplied later in a workflow instead of long if then statements?  

For example, let's say I'm assigning values to someone's age and value of their RV.  I have a file of customer IDs, their ages and RV values.  I have a file of predicted values for each age (the look up table).  The intent is to assign the correct value to the column in the customer file and then multiply those two values together.  I've tried cross-tabbing the predicted values and joining but then nothing joins.  

The model is done outside of Alteryx so I cannot use the score tool. 

This is an easy solution in R or python but I would like to put one together in Alteryx for easier use among coworkers. 

I've attached the exported workflow with example data inside it.  

Thank you. 

[mceleavey]

Hi @jbh1128d1,

I've attached the amended workflow. You don't have complete data-sets so not all records match, but I assume that's because it's just a small selection.

Let me know if that's what you're after.

M

[jbh1128d1]

Thank you @mceleavey. 

The complete data set won't match the predicted variables because there may not be customers that match all variable levels that have predicted values.  The actual model actually has 14 to 25 different variables.  Will I have to do a filter and join tool for each variable? 

[mceleavey · Accepted solution]

Hi @jbh1128d1,

Ah, I see.

Given the first stream is the one which will not dynamically expand according to the numbers of a given variable, it is this I would pivot.

I've attached the new workflow, but basically it transposes the customers and then links on the dual fields. This allows the links to be dynamic should it link on one, two, five etc. variables.

I've then taken a left outer join by creating a union of the left and the join outputs. This will give you a row, with each field to be used in the calculation, for each variable. You can then apply the calculations in a formula tool and cross-tab the data again to get a single row per customer.

Let me know if this helps.

M.

[ponraj · Accepted solution]

Attaching a sample workflow for your case.  Hope this helps. 

WorkflowResults

[jbh1128d1]

Thanks @mceleavey.  

[jbh1128d1]

Thank you @ponraj