Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

jbh1128d1
10 - Fireball

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. 

 

 

6 REPLIES 6
mceleavey
17 - Castor
17 - Castor

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



Bulien

jbh1128d1
10 - Fireball

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
17 - Castor
17 - Castor

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.



Bulien

ponraj
13 - Pulsar

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

WorkflowWorkflowResultsResults

jbh1128d1
10 - Fireball

Thanks @mceleavey.  

jbh1128d1
10 - Fireball

Thank you @ponraj

Labels