Calculation in main dataset using 2 reference tables

[rosu]

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").

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

 

 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':

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!

[neilgallen · Accepted solution]

@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.