How can I join two datasets using a key in OpenRefine, with the secondary table having more than one value?

Question

I have a dataset X like this:

Code | Name
------------
123  | AAA
456  | BBB

And the other Y like this:

Code | Level
------------
123  | A
123  | B
456  | B
456  | C

I want to join them using OpenRefine to something like this:

Code | Name | Level A | Level B | Level C
------------------------------------------
123  | AAA  |  value  |  value  |   -
456  | BBB  |    -    |  value  | value

When I try to add a column using cell.cross() from 'X.Code' it only gets the value from the first appearance of 'X.Code' in 'Y'.

cell.cross("Y", "Code")[0].cells["Rede"].value[0]

How can I get to this desired output, using GREL?

Answer 1

You need Columnize by key/value your project Y to have one column by Level like the example below. Use Transpose -> Columnize by key/value

Code | Level A | Level B | Level C
------------------------------------------
123  | value  |  value  |   -
456  |   -    |  value  | value

Then you can use the cell.cross function for each column. For example: cell.cross("Y", "Code")[0].cells["Level A"].value[0] to import the data into the Project X