Mapping load based on another field

[simotrab]

Hi community,

I'm stuck in a mapping load that I cannot manage.

The data I got are something like:

LOAD *

Inline

[data,name,

'IT','Italy'

'DE','Germany'

'FR','-'

'US','-'

];

The "name" field is incomplete as you can see. I'd like to have this result.

LOAD *

Inline

[data,name,

'IT','Italy'

'DE','Germany'

'FR','France'

'US','United States of America'

];

I've read a bit of documentation

ApplyMap - script function ‒ Qlik Sense

I've thought that I need to map the table basing the mapping on the field "data", but, due my few experience with mapping, I cannot do it.

Thanks for your time

[sunny_talwar]

What do you mean based on a third field? The sample data you shared has only 2 fields? Can you elaborate a little?

[simotrab]

Hi, sorry for my late answer!

I'll try to explain it better: I need that for each row of the "data" column, the "name" column that is blank ( - ), is going to to have the right name of the nation.

I have tried to use the mapping but it is going to replace the "-" ignoring the "data" column.

I'm going to edit the question to make it more understandable.

Thanks

[sunny_talwar]

I have tried to use the mapping but it is going to replace the "-" ignoring the "data" column.

Can you share the exact script you have used?

[simotrab]

Sure, I have not post it because it was clearly an error using the mapping, but I cannot imagine how to fix it:

map1:

mapping LOAD *

Inline [

name, correction

'-', 'France'

'-', 'United States of America'

] ;

LOAD *,

ApplyMap('map1', name) As test

Inline

[data,name,

'IT','Italy'

'DE','Germany'

'FR','-'

'US','-'

];

drop field name;

As you can see, the result is going to have all  the '-' changed in "France", that is not what I would like.

[sunny_talwar · Accepted solution]

Try this out

map1:

Mapping

LOAD * Inline [

name, correction

'FR', 'France'

'US', 'United States of America'

] ;

LOAD *,

  ApplyMap('map1', data, name) As test

Inline

[data,name,

'IT','Italy'

'DE','Germany'

'FR','-'

'US','-'

];

DROP Field name;

[simotrab]

Yes, I've thought something like that, but you have done it fastly and perfectly. Many thanks!