Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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
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;
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.
Hi Steve,
This works exactly how I needed.
Thanks so much.
Amit.