Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Apply Map / If Wildmatch to find text within text string

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

];

1 Solution

Accepted Solutions
sunny_talwar

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)

];

Capture.PNG

View solution in original post

3 Replies
sunny_talwar

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)

];

Capture.PNG

Anonymous
Not applicable
Author

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

mahendrankr
Contributor III
Contributor III

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