Search a list in a column

[ecems]

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)

[vinieme12]

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

[Lech_Miszkiewicz]

Hi, If you do this during load you could:

• Create map like

map_Campaign:

Mapping Load Distinct

FIRM,1

From (your SQL query...);

• Then apply map when loading FIRM

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

[ecems]

Thanks for  your reply, no they are not in the same table.

Fe:

FIRM LIST TABLE:

FIRM

• A
• B
• C 
• D 

CAMPAIGN INFO TABLE:

CAMPAIGN

• A CAMPAGIN
• FIRM A CAMPAIGN
• A
• FIRM A
• B
• FIRM B CAMPAIGN

I WANT THIS RESULT:

A CAMPAGIN	match with campaing
FIRM A CAMPAIGN	match with campaing
A	match with campaing
FIRM A	match with campaing
B	match with campaing
YXT	no match
FIRM X	no match

[vinieme12]

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
];

[ecems]

Should I write it load editor?

[vinieme12]

Yes, this must be in LOAD SCRIPT 

[ecems]

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
];

[vinieme12]

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;