topic Re: Help with using applymap in QlikView

Help with using applymap

amit_shetty78 — Tue, 17 Dec 2013 15:27:17 GMT

Hi All,

I have a scenario where I have a country column which can have a comma separate list of countries like:

Id Country

1 'Italy, Denmark, Ireland, South Africa'.

This can be any number of countries:

I would like to have a list box of all countries where when I select say 'Denmark', the row with Id=1 should be highlighed, Same with any of the other 3 countries. For that I tried using applymap:

Country_Map:

Mapping load * INLINE [

Country CountryNew

'Italy, Denmark, Ireland, South Africa' Italy

'Italy, Denmark, Ireland, South Africa' Denmark

'Italy, Denmark, Ireland, South Africa' Ireland

'Italy, Denmark, Ireland, South Africa' South Africa

Load

Id,

ApplyMap('Country_Map', Country) As Country

FROM x;

This only maps the comma separate country list to the first country, in this case Italy.

An I doing something incorrect here? Is the right way to use ApplyMap? Is there a work around?

thanks.

Steve Dark

Re: Help with using applymap

stevedark — Tue, 17 Dec 2013 15:34:01 GMT

Hi Amit,

ApplyMap won't help you in that case, as it will only ever assign one value to a field.

The reason your mapping table is not giving the result you are expecting is because the comma in the Country name is being seen as the delimiter in the INLINE table - you can not override the comma using quotes here.

What you need to do is use the SubField statement.

Load your main table with all fields other than the Country.

Then do a separate load from the same field, with only the Id and a SubField statement on the Country:

Countries:

LOAD

Id,

SubField(Country, ',') as Country

FROM....

You will find the second table will have one row per country linking back to the main table. This will then behave as you want it to.

Hope that helps.

Steve

Re: Help with using applymap

Anonymous — Tue, 17 Dec 2013 15:42:32 GMT

First you should use double quotes around the different countries and also I probably would separate the Inline table from the mapping load.

Country:

load * INLINE [

Country , CountryNew

"Italy, Denmark, Ireland, South Africa" Italy

"Italy, Denmark, Ireland, South Africa" Denmark

"Italy, Denmark, Ireland, South Africa" Ireland

"Italy, Denmark, Ireland, South Africa" South Africa

];

mCountry:

Mapping Load

Country ,

CountryNew

Resident Country;

Then you can do the applymap

Re: Help with using applymap

stevedark — Tue, 17 Dec 2013 17:09:35 GMT

Nils,

I can't see how that would work. FIrstly, you seem to be missing commas in the INLINE load, but even if that did load correctly then any time that the Country string was found it would map to Italy - as ApplyMap will always map to the first entry it finds in the mapping table.

Or am I missing something here?

Steve

Re: Help with using applymap

Anonymous — Tue, 17 Dec 2013 18:45:59 GMT

Steve,

No, you are correct. I was a bit quick and missed the commas but apart from that I was only looking at the Applymap part and how to load the value "Italy, Denmark, Ireland, South Africa". As you mentioned this will not actually solve Amits problem. Using your solution it will look something like this, now in this example I am using an inline table but this could be from a .csv file or something else, also the Id value is 1 for all countries and I am not sure if this is the actual case.

load * INLINE [
Country     ,                                   Id
"Italy, Denmark, Ireland, South Africa" ,       1
"Italy, Denmark, Ireland, South Africa" ,       1
"Italy, Denmark, Ireland, South Africa" ,      1
"Italy, Denmark, Ireland, South Africa" ,      1
];
Countries:
LOAD
   Id,
   SubField(Country, ',') as Countryrename
Resident Country_tmp;
Drop Table Country_tmp;

Re: Help with using applymap

amit_shetty78 — Wed, 18 Dec 2013 12:03:02 GMT

Hi Steve,

Thanks you for the suggestion. Will try using the SubField function and do 2 loads.

Will let you know how that goes.

Thanks Again.

Amit.

Re: Help with using applymap

amit_shetty78 — Thu, 19 Dec 2013 14:02:19 GMT

Hi Steve,

This works exactly how I needed.

Thanks so much.

Amit.