Use Match-Function with Field

[SamuelHaag]

Hello,

is there a workaround to use the match-function (in script) with a field instead of lots of strings?

At the moment I'm using the following line, which is working:

Where Match (FCURR, 'XAF','XOF', 'RMB', 'TRL', 'BYR', 'VEF', 'VES','CHF', 'SAR', 'IDR','EUR','ATS','BEF','DEM', 'DM','ESP','FIM','FRF','IEP','ITL','GRD','LUF','NLG','PLZ','PTE','SKK','VEB', 'XEU','RBL');

But I would like to have something like this, where "MATCH_FIELD" contains all the Strings:

Where Match (FCURR,  MATCH_FIELD);

So I can load the match-values from a Excel-file and a user can maintain them

Thank you

Regards

Samuel

[MayilVahanan · Accepted solution]

HI @SamuelHaag 

Try like below

Temp:
LOAD chr(39)&Concat(FCURR,Chr(39)&','&Chr(39))&Chr(39) as FCURR
FROM
[D:\My Personal\QV\FCURR.xls]
(biff, embedded labels, table is Sheet1$);

Let vFCURR = Peek('FCURR');

DROP Table Temp;

Load * from ursource
where Match(FCURR, $(vFCURR));

or use exists function

Temp:
LOAD FCURR
FROM
[D:\My Personal\QV\t.xls]
(biff, embedded labels, table is Sheet1$);

Load * from ursource
where Exists(FCURR, FCURR);

DROP Table Temp;

[bramkn]

Or use an inner join, this should be faster according to Qlik.

Table:
LOAD FCURR
FROM
[D:\My Personal\QV\t.xls]
(biff, embedded labels, table is Sheet1$);

inner Join(Table)
Load * from ursource;

[SamuelHaag]

Thank you guys! That helped a lot 😃

Regards 

Samuel