performing multidimensional lookups

sgenzer
Altair Employee

Hi all -
As part of my ETL, I wish to take two or more attributes and map them to a lookup table, find a particular value, and then add that new value back into a new attribute. In Excel, it looks like this (with some random reference cells):
=INDEX(Sheet1!$A$2:$BO$9999,(MATCH($E3&$G3&$J3,Sheet1!$C$2:$C$9999&Sheet1!$B$2:$B$9999&Sheet1!$E$2:$E$9999,0)),24)
So this uses an array on Sheet1 as a multidimensional lookup table. It then takes the values of E3, G3 and J3 and looks them up on Sheet1 in columns C, B and E. When it finds a match, it goes to column 24 and returns the value of that cell.
How can I do this on RM?
Thanks!
Scott
As part of my ETL, I wish to take two or more attributes and map them to a lookup table, find a particular value, and then add that new value back into a new attribute. In Excel, it looks like this (with some random reference cells):
=INDEX(Sheet1!$A$2:$BO$9999,(MATCH($E3&$G3&$J3,Sheet1!$C$2:$C$9999&Sheet1!$B$2:$B$9999&Sheet1!$E$2:$E$9999,0)),24)
So this uses an array on Sheet1 as a multidimensional lookup table. It then takes the values of E3, G3 and J3 and looks them up on Sheet1 in columns C, B and E. When it finds a match, it goes to column 24 and returns the value of that cell.
How can I do this on RM?
Thanks!
Scott
Tagged:
0
Best Answer
-
I agree with @awchisholm this seems like a pretty straightforward Join task using a compound key.1
Answers
-
Hello
The Join operator would be one way. Use the example set containing the thing to look for as the Left Join with the Right Join as the reference data. You can set multiple index parameters to allow look ups with compound keys. Set the join type as inner join. The result will be a single example that can be manipulated although it's difficult without the details of your process and data to know what to do.
regards,
Andrew1 -
I agree with @awchisholm this seems like a pretty straightforward Join task using a compound key.1