topic Re: Apply Map / If Wildmatch to find text within text string in QlikView

Apply Map / If Wildmatch to find text within text string

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

];

Re: Apply Map / If Wildmatch to find text within text string

sunny_talwar — Wed, 11 Jul 2018 13:30:48 GMT

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)

];

Re: Apply Map / If Wildmatch to find text within text string

Anonymous — Wed, 11 Jul 2018 15:34:43 GMT

Brilliant, thank you very much for your help Sunny. It worked perfectly.

Re: Apply Map / If Wildmatch to find text within text string

mahendrankr — Mon, 22 Mar 2021 11:52:38 GMT

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