community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Calculation in main dataset using 2 reference tables

Atom

Hello everyone

 

There is a slight problem that I cannot figure out how to solve. Any advice would be welcome.

 

I have a main dataset which is simply a table with 'ID' of an observation (similar to Record ID in Alteryx) and several 'Type' columns that have categorical values (either "C1", "C2" or "C3").

 

 

image.png

 

 

There are also 2 reference tables that should be used in the calculation. One is referring to Types and another is referring to Categories:

 image.png

 

image.png

 

 My calculation consists of getting a 'ID Value' for each ID that is equal to the sum of all Types multiplied by Category. For example for ID 10001 we:

  1. take Type A (value of which in the reference table equals to 2)
  2. take respective category - C1 (value of which in the reference table equals to 1)
  3. multiply Type and Category: 2 * 1 = 2
  4. repeat steps 1-3 for each Type (column)
  5. sum up all resulting values. This will be our 'ID Value' for ID 10001.

Then we just repeat same steps for each ID. The final result should be the same main table but with an additional column 'ID Value':

 

image.png

 

My first idea was to use Multi-Field Formula, but I'm not sure if it is possible to have several data sources (main table + 2 reference tables) in it. Feel free to propose other (maybe more efficient) ideas!

 

 

Apologies in advance if something isn't clear; non-native speaker here. Thank you in advance for all the help!

Bolide

@rosu If I'm understanding the example correctly, then the attached workflow should do what you're looking for. The trick is the transpose at the beginning. Essentially after that you're just looking at the associated reference tables and adding up values. A simple join back to the original dataset is all that's required.

 

ID Value Example.PNG

Labels