Trouble with Mapping Load and WildMatch

[RJW]

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.

[mansoorsheraz]

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.

[marcus_sommer · Accepted solution]

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

[RJW]

Hi Marcus, thanks so much for your comment.  This is exactly what I was looking. The data returns as expected!

[RJW · Accepted solution]

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!