Loading a mapping which has a custom wildcard inside

[#1]

Hi  I have a mapping as attached. And the corresponding salesman and region fields in my raw data. I currently have the following load statement :

LOAD

    [Salesman],

          Subfield( ApplyMap('ATK_map_P2', [Salesman], [Region1] & '|'),'|',1) as Region1_New,

          Subfield( ApplyMap('ATK_map_P2', [Salesman], '|' & [Region2]),'|',2) as Region1_New,

          Subfield( ApplyMap('ATK_map_P2', [Salesman], '||' & [Region3]),'|',3) as Region1_New,

FROM ....

The twist is however that the percentage signs in the mapping are basically wildcards. So DAVID% in the mapping basically means match against the salesman field in the raw data if it contains the word david.

Is this something possible to script in ? 

[sunny_talwar]

May be something along these lines:

MappingTable:

Mapping

LOAD * Inline [

Name, Region

David, %USA%

Rahul, %India%

];

Fact:

LOAD *,

  TextBetween(MapSubString('MappingTable', Salesman), '%', '%') as Region;

LOAD * Inline [

Salesman, Sales

David_R_Johnson, 10

David_K_McGrath, 15

Rahul_Gandhi, 20

];

[#3]

Apologies I forgot to add in my mapping table. Should make things clearer.

[Gysbert_Wassenaar]

Perhaps you can use this: mapping with wildcards | Qlikview Cookbook