Help with using applymap

[amit_shetty78]

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

[stevedark]

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

[Anonymous]

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

[stevedark]

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

[Anonymous]

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;

[amit_shetty78]

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.

[amit_shetty78]

Hi Steve,

This works exactly how I needed.

Thanks so much.

Amit.