Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a wildcarded lookup table that I want to apply to the data coming in from another table.
Field 1 | Field 2 | Field 3 | Category |
A | 10 | C | Green |
A | 10 | * | Orange |
A | 37 | A | Blue |
A | * | * | Purple |
C | * | J | Pink |
J | * | * | Red |
* | * | * | Black |
The table should be read top-down, left to right. * means all other values or all values if the row above it doesn't have a specific value in it. Field 1 has a range of values from A-Z. Field 2 has a range of values from 0-9, Field 3 has a range of values from A-Z.
The result I'm looking for is the following in the data I'm applying this mapping table to.
Field 1 | Field 2 | Field 3 | Category |
A | 44 | J | Purple |
A | 10 | C | Green |
A | 10 | X | Orange |
B | 45 | D | Black |
C | 1 | K | Black |
J | 11 | Y | Red |
C | 1 | J | Pink |
Can I use ApplyMap or something like it to accomplish this?
Hi,
the way you want to use it it is not possible. You have to use some kind of If statement for your example it could be sometling like this:
If(Field1='A', If(Field2=10,If(Field3='C','Green','Orange'),If(Field2=37,If(Field3='A','Blue','Purple'),'Purple')), If(Field1='C',If(Field3='J','Pink','Black'),If(Field1='J','Red','Black'))) as Category
Hope this helps 😉