Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Cross referencing in script

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.

IDCOUNTRYREGIONPARENTID

1

SwedenEurope
2Norway1
3ChinaAsia

Any help would be much appreciated.

Tags (1)
1 Solution

Accepted Solutions

Re: Cross referencing in script

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.


talk is cheap, supply exceeds demand
2 Replies

Re: Cross referencing in script

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.


talk is cheap, supply exceeds demand
Not applicable

Re: Cross referencing in script

Works great!

Thank you so much for your help!

Community Browser