Hello everyone,
I am struggling with reproducing a standard Excel scenario in Alteryx.
I have two tables. A Data Table containing the following columns, intially with an empty "Target" column.
DATA TABLE
ID | Kind | Value | Target |
1 | a | 11 | 10 |
2 | a | 13 | 14 |
3 | b | 19 | 20 |
4 | a | 17 | 18 |
5 | b | 15 | 16 |
6 | b | 17 | 16 |
7 | a | 3 | 2 |
8 | a | 5 | 6 |
9 | b | 5 | 4 |
10 | a | 7 | 6 |
11 | b | 9 | 8 |
12 | a | 15 | 14 |
13 | a | 1 | 2 |
The second table is the reference table, from which the target value should be pulled. "Target" depends on the kind and should then match the "Value" field.
REFERENCE TABLE
Value | Kind a | Kind b |
1 | 2 | 1 |
3 | 2 | 4 |
5 | 6 | 4 |
7 | 6 | 8 |
9 | 10 | 8 |
11 | 10 | 12 |
13 | 14 | 12 |
15 | 14 | 16 |
17 | 18 | 16 |
19 | 18 | 20 |
In excel that would be something like:
=if(Kind="a";vlookup(Value;REF-TABLE;2);vlookup(Value;REF-TABLE;3))
How can I populate the Datea-Table / Target column with the correct values from the Reference Table?
Many thanks in advance!
Solved! Go to Solution.
Hi Alan, many thanks that does the trick!