
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Trouble with Mapping Load and WildMatch
Hi, I have a what should be a simple problem, but I just can't seem to solve it.
Simplifying this a bit, but I have a table of transactions which contains various attributes. For each transaction, there is a description. The data looks like this:
ClientID | Transaction | Description |
001 | 1 | High value |
001 | 2 | Medium value |
001 | 3 | Low value |
002 | 1 | Low value |
If a client ID has a description that contains "high value", I would like to flag that client ID. So, I have done a Mapping Load using WildMatch (as each description isn't as clean as above). So, the code looks like this:
HighValueFlag:
Mapping LOAD
"ClientID",
if(WildMatch("Description", '*High*'), 'Y', 'N') as Flag;
SQL ....
FinalTable:
"ClientID",
"Transaction",
ApplyMap ('HighValueFlag', "ClientID", 0) as Flag;
SQL....
I am pulling from the same table in the database using SQL that's why I have the proceeding loads. I wouldn't expect that to be a problem but I am not sure.
But, basically, I want the final data to look like and apply the Y flag to all instances of the client ID so I can toggle on/off "high value" clients:
ClientID | Transaction | Description | Flag |
001 | 1 | High value | Y |
001 | 2 | Medium value | Y |
001 | 3 | Low value | Y |
002 | 1 | Low value | N |
Any direction on this? As it stands now, the data is just returning N for all Client IDs when using the Apply Map.
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
A mapping in Qlik behaved like a vlookup in Excel and returned always the value from the first match. But your clients don't have only one transaction else n ones.
In many scenarios you would need to aggregate your n values at first to calculate with them against the grouping dimension. But in this case you could avoid it by filtering the mapping table. This may look like;
HighValueFlag:
Mapping LOAD
"ClientID",
'Y' as Flag where WildMatch("Description", '*High*');
SQL ....
FinalTable:
"ClientID",
"Transaction",
ApplyMap ('HighValueFlag', "ClientID", 'N') as Flag;
SQL....
- Marcus

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi, this works!
I ended up going with Marcus's solution as it was easier to implement but thanks for your solution as well. Very nice!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
Here is the resultant table from the script used below:
This may solve your issue
[temp]:
Load * inline [
ClientID, Transaction, Description
001, 1, High value
001, 2, Medium value
001, 3, Low value
002, 1, Low value
];
[values]:
Load
ClientID,
ClientID as CLID,
'Y' as Flag
Resident [temp]
where Description = 'High value';
Concatenate ([values])
Load
ClientID,
'N' as Flag
Resident [temp]
where not Exists("CLID", ClientID);
[TempFinal]:
NoConcatenate
Load
ClientID,
Transaction,
Description
Resident [temp];
Join
Load
ClientID,
Flag
Resident [values];
[Final]:
NoConcatenate
Load ClientID,
Transaction,
Description,
Flag
Resident [TempFinal];
drop table [temp];
drop table [values];
drop table [TempFinal];
Regards.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
A mapping in Qlik behaved like a vlookup in Excel and returned always the value from the first match. But your clients don't have only one transaction else n ones.
In many scenarios you would need to aggregate your n values at first to calculate with them against the grouping dimension. But in this case you could avoid it by filtering the mapping table. This may look like;
HighValueFlag:
Mapping LOAD
"ClientID",
'Y' as Flag where WildMatch("Description", '*High*');
SQL ....
FinalTable:
"ClientID",
"Transaction",
ApplyMap ('HighValueFlag', "ClientID", 'N') as Flag;
SQL....
- Marcus

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Marcus, thanks so much for your comment. This is exactly what I was looking. The data returns as expected!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi, this works!
I ended up going with Marcus's solution as it was easier to implement but thanks for your solution as well. Very nice!
