Create Flag based on Value

[has75042]

Hi There,

In Table 1, I have 3 fields

Store Number

Cash  

Check 

CreditCard

I have the same fields in table 2. I want to create a flag based on the  match value

For example:

If table 1 cash value matches with table 2 cash value, the flag should say 'Yes.' 

If table 1 check value doesn't match with table 2 check value, the flag should say 'No' 

Any help would greatly appreciate it. 

[JeromeS]

Hi Has,

Safest solution for me is applymap

if you want to add a flag in table 1: 

First create a mapping table using mapping LOAD with data from table 2 (https://help.qlik.com/en-US/sense/September2020/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPre...)

then in table 1 add a flag field with the following script formula:

if(isnull(applymap('mappingtablename',table1fieldtocheck,null())),'no','yes') as flag

if isnull(applymap(..)) returns true, then no match (and field is no), else there is a match and field is yes

hope it helps !

Jérôme

[has75042]

Hey Jerome

With the below logic the flag is not working the way it supposed.

Store_Mapping:
Mapping LOAD
FacilityId,
trim(num(Store_Number)) as [Store Number]
;

Exception_Mapping:
Mapping LOAD
"Store",
Cash
FROM [lib://AttachedFiles/Exception Report Data.xlsx]
(ooxml, embedded labels, table is Sheet1);

[Management Summary]:
Load*,
if(isnull(applymap('Exception_Mapping',[Store Number],null())),'no','yes') as flag;
LOAD
FacilityId,
ApplyMap('Store_Mapping', FacilityId, Null()) as [Store Number],

Cash,
Check,
CreditCard
;

[has75042]

Hi There,

Is there any I can create a flag based on Value.

For example: In the Management Summary table Cash Value is $100 and in Exception Mapping, the cash field value is $100. The flag should say Yes.

Example 2: In the Management Summary table Cash Value is $200 and in Exception Mapping, the cash field value is $150. The flag should say No. 

[has75042 · Accepted solution]

This Solution worked for me.

Temp:

Column 1  & '_'&   Column 2 as Key

Column 2

Join(Temp)

Column 1 & '_' &  Column 2 as Key

Column 4

Final:

Key,

If(Column2 = Column 4, 'Yes', 'No') as Flag

Resident Temp;

Drop Table Temp;