Options
performing multidimensional lookups
sgenzer
Administrator, Moderator, Employee, RapidMiner Certified Analyst, Community Manager, Member, University Professor, PM Moderator Posts: 2,959 
Community Manager
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
0
Best Answer
-
Options
Telcontar120
Moderator, RapidMiner Certified Analyst, RapidMiner Certified Expert, Member Posts: 1,635 
Unicorn
I agree with @awchisholm this seems like a pretty straightforward Join task using a compound key.6
Answers
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,
Andrew