Cross referencing in script

[#1]

Hi!

I'm sure that I'm creating a post that there already is an answer for but I can not find it.

I have a table that looks like the one below. What i want to do in the script is to look at the parent ID field. If there is a value I want it to find that row based on the ID field, copy the region value and put it in the region field for the row that has a parent ID.

So, in this example I want Norway to inherit the region value Europe from its parent Sweden.

ID	COUNTRY	REGION	PARENTID
1	Sweden	Europe
2	Norway		1
3	China	Asia

Any help would be much appreciated.

[Gysbert_Wassenaar · Accepted solution]

You can use a mapping table and the applymap function:

MapRegion:
mapping LOAD * INLINE [
    ID, REGION,
    1, Europe
    2,
    3, Asia
];

Result:
LOAD ID, COUNTRY, applymap('MapRegion',PARENTID,REGION) as REGION, PARENTID INLINE [
    ID, COUNTRY, REGION, PARENTID
    1, Sweden, Europe,
    2, Norway, , 1
    3, China, Asia
];

See attached qvw.

Note, the inline loads are just examples. You can create the tables from loads from files or databases just as well.

[#3]

Works great!

Thank you so much for your help!