Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone;
I have a firm list (Column name: FIRM) and I load it to qlik; and i have another SQL query and i want to search this list in a column of this query (Column name: CAMPAIGN).
I tried different solutions but it they didnt work. Can you help me?
Thanks!
=IF( WILDMATCH (CAMPAIGN, FIRM), 1, 0)
=IF( LIKE (CAMPAIGN, FIRM), 1, 0)
=IF( LIKE (CAMPAIGN, '$FIRM'), 1, 0)
=IF( LIKE (CAMPAIGN, '*'$FIRM'*'), 1, 0)
are the fields CAMPAIGN and FIRM in the SAME TABLE?
can you post some sample data and expected output so its easier to understand what you are trying to achieve
Hi, If you do this during load you could:
map_Campaign:
Mapping Load Distinct
FIRM,1
From (your SQL query...);
Load
CAMPAIGN,
ApplyMap('map_Campaign',CAMPAIGN,0) as flag_match
From (your source of SQL query);
If you want to do this in UI first you need to convert your list of firms into array (you can use Concat() function to do this.
cheers
Thanks for your reply, no they are not in the same table.
Fe:
FIRM LIST TABLE:
FIRM
CAMPAIGN INFO TABLE:
CAMPAIGN
I WANT THIS RESULT:
|
match with campaing |
|
match with campaing |
|
match with campaing |
|
match with campaing |
|
match with campaing |
|
no match |
|
no match |
as below
CheckFirm:
Mapping load FIRM,'@EXISTS@' as Sub INLINE [
FIRM
A
B
C
D
];
load *,if(Index(MapSubString('CheckFirm',CAMPAIGN),'@EXISTS@'),'matching','no match') as Match Inline [
CAMPAIGN
A CAMPAGIN
FIRM A CAMPAIGN
A
FIRM A
B
FIRM B CAMPAIGN
YXT
FIRM X
];
Should I write it load editor?
Yes, this must be in LOAD SCRIPT
Thanks a lot, i have a little problem; i dont want to write campaign names; i want to use a column of a table (column name is CAMPAIGN NAME)
as Match Inline [
CAMPAIGN
A CAMPAGIN
FIRM A CAMPAIGN
A
FIRM A
B
FIRM B CAMPAIGN
YXT
FIRM X
];
This would be as below
Consider C
ampaignTable has field CAMPAIGN NAME
and FirmTable has field FIRM NAME
__________________________
FirmTable:
Load [FIRM_NAME],dim1,dim2,otherfield1,otherfield2
From FirmTableSource;
CheckFirm:
Mapping Load Distinct FIRM_NAME ,'@EXISTS@' as Sub
Resident FirmTable;
CampaignTable:
Load [CAMPAIGNNAME],dim1,dim2,otherfield1,otherfield2
,if(Index(MapSubString('CheckFirm',CAMPAIGN),'@EXISTS@'),'matching','no match') as Match
From CampaignTableSource;