Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
What i am trying to do i thought would be easy, but I have stuck on this for a while. (i am an ameture user of Qlivkiew, so please remember this when answering).
I have 2 tables, one called Nationality Group which contains 2 fields, a "clean"/master list of Nationalities and a Nationality group to show if that nationality is in the EU, British or Non EU. My second table is the nationalities that have been entered against employees and therefore could contain extra text and a mix of upper and lower letters. (I accept that any spelling mistakes of Employee Nationalities won't get the Nationality group applied e.g Frenhc).
I am trying to apply the Nationality Group to the Employee Nationality and have used apply map to achieve this. The problem is i don't know how to get the Nationality group to apply to the Employee Nationality where there is the extra text. e.g find the word French within text string.
The tables below only contains example data and in reality there are over 200+ master nationalities to check against 3000+ employees. I have seen on the forum people using if(wildmatch()) etc, but in all examples they specify the text to match rather than it coming from another table/applymap, so please help!!
Nationality Group:
Load * Inline [
Nationality, Nationality Group
Albanian, Non EU
British, British
French, EU
];
Employee Data:
Load * Inline [
Employee Number, Employee Nationality
1, 6/2/ 09 British
2, 21/05/ 17 british
3, Albanian
4, ALBANIAN (HR CHecked)
5, albanian, work visa, pasport, 13.03.09 14.04.11
6, British
7, French
8, 15/1/18 french (checked)
];
Final output should look like below....
Employee Data:
Load * Inline [
Employee Number, Employee Nationality, Nationality Group
1, 6/2/ 09 British, British
2, 21/05/ 17 british, British
3, British, British
4, Albanian, Non EU
5, ALBANIAN (HR CHecked), Non EU
6, albanian, work visa, pasport, 13.03.09 14.04.11, Non EU
7, French, EU
8, 15/1/18 french (checked), EU
];
Try this
[Nationality Group]:
Mapping
LOAD Upper(Nationality),
'@' & [Nationality Group] & '~';
LOAD * INLINE [
Nationality, Nationality Group
Albanian, Non EU
British, British
French, EU
];
[Employee Data]:
LOAD *,
TextBetween(MapSubString('Nationality Group', Upper([Employee Nationality])), '@', '~') as [Nationality Group];
LOAD * INLINE [
Employee Number, Employee Nationality
1, 6/2/ 09 British
2, 21/05/ 17 british
3, Albanian
4, ALBANIAN (HR CHecked)
5, "albanian, work visa, pasport, 13.03.09 14.04.11"
6, British
7, French
8, 15/1/18 french (checked)
];
Try this
[Nationality Group]:
Mapping
LOAD Upper(Nationality),
'@' & [Nationality Group] & '~';
LOAD * INLINE [
Nationality, Nationality Group
Albanian, Non EU
British, British
French, EU
];
[Employee Data]:
LOAD *,
TextBetween(MapSubString('Nationality Group', Upper([Employee Nationality])), '@', '~') as [Nationality Group];
LOAD * INLINE [
Employee Number, Employee Nationality
1, 6/2/ 09 British
2, 21/05/ 17 british
3, Albanian
4, ALBANIAN (HR CHecked)
5, "albanian, work visa, pasport, 13.03.09 14.04.11"
6, British
7, French
8, 15/1/18 french (checked)
];
Brilliant, thank you very much for your help Sunny. It worked perfectly.
Hi Sunny,
This really helped me on a different case. However I have a minor catch. From the below table when i follow exactly your script, I get "Other_Beauty" as Business for both the campaign's. Is there any way that this applymap works as per the order of the keywords given. ie first it looks for Hair and then map those campaigns to "Beauty" instead of "Other_Beauty". Can you pl help
Keyword:
Load * Inline [
Keyword, Business
Hair, Beauty
BEA, Other_Beauty
];
Campaign Data:
Load * Inline [
Campaign_Name
1, LV_BEA_HairDryers
2, CZ_BEA_HairDryers
];
Below is the script I used.
[Keyword]:
Mapping LOAD
([Keyword]),
'@' & [Business Units] & '~';
LOAD *,
[Words to Lookup for],
[Business Units]
FROM [lib://AttachedFiles/Keyword.xlsx]
[Campaign Data]:
LOAD *,
TextBetween(MapSubString('Keyword',([Campaign name])), '@', '~') as [Business Units];
LOAD
[Campaign name],
FROM [lib://AttachedFiles/CampaignFile.xlsx]
Exit Script