Using a list of values to create a flag using match and wild match

[jeckstein]

Hi All and thanks in advance for any help!

I have a table called data. This table has two fields. ID and CODE. I want to create a third field called Matched_Flag.

I also have two other tables. The first is Exact_Match which has one field called exact_match.  The other table is called Wild_Match which has 1 field called wild_match.

Data:

ID	Code	Matched_Flag
1	A1
2	A2
3	A3
4	A4
5	B1
6	C1
7	C2

Exact_Match:

Exact_Match
A1
A2
C1

Wild_Match:

Wild_match
B

Matched flag should be populated with a 1 or a 0.

It should be populated with a 1 if there is an exact match in from the Code field in the data table to the exact_match field in the exact_match table.

OR

It should be populated with a 1 if there is an wild match in from the Code field in the data table to the wild_match field in the wild_match table.

And I cannot simply add values in the backend of the application to a match or wild match function because those people updating the exact_match and wild_match tables will not have access to the qlik script

I already have a solution to do this which is the following

1. Use a concat() function to create a sting of all the values for both the exact_match and wild_match fields.
2. create  2 variables using using peek function and the fields created in step 1
3. use a match or wild match function on the variables and the code table

I am wondering if there is an easier way to do this. It seems like it is complicated.

[vamsee]

Hi James,

I couldn't understand the part of user's updating the match and wildmatch tables. Hoping that they would be updating them in a different file which is being read as a source.

I have implemented the data sources in the form of Inline tables.

Qlik Mapping does not take wild cards. Exact Match can be implemented using ApplyMap directly, but for WildMatch considering the data you provided for Wild Match Table

Try

Exact_Match_Map:
Mapping LOAD Exact_Match, 1 as Flag;
LOAD * Inline [
Exact_Match
A1
A2
C1
];
Wild_Match_Map:
Mapping LOAD  Wild_match,1 as Flag;
LOAD * Inline [
Wild_match
B,
C
];


Data:
LOAD
ID,
Code,
ApplyMap('Exact_Match_Map', Code,0) as Exact_Matched_Flag,
ApplyMap('Wild_Match_Map', purgechar(Code,'0123456789') ,0) as WildMatched,
ApplyMap('Exact_Match_Map', Code, ApplyMap('Wild_Match_Map',purgechar(Code,'0123456789') ,0)) as Matched_Flag

/** Final Required Flag **/

;
LOAD * INLINE [
ID, Code, Matched_Flag
1, A1,
2, A2,
3, A3,
4, A4,
5, B1,
6, C1,
7, C2,

];