topic Re: Excel MATCH function in Qlikview in QlikView

Excel MATCH function in Qlikview

Anonymous — Tue, 03 Nov 2015 21:47:00 GMT

Could anyone help me achieve the same result in qlikview? I am after the records where the values in column D do not appear in

column G at all. So I am more interested in the #N/a results than the number of matches found.

Re: Excel MATCH function in Qlikview

Tue, 03 Nov 2015 22:55:57 GMT

Chantelle,

Can you try the below expression.

sum(if(mas_no<>formula or IsNull(mas_no)=-1,1,0))

which gives you the count of Long_no that have formula not matching with mas_no or having a null mas_no.

Thanks

Brad.

Re: Excel MATCH function in Qlikview

Anonymous — Tue, 03 Nov 2015 23:08:57 GMT

Hi Brad

That does not work as it only compares values in the same 'row' or 'record'.

I need it to compare D2 (formula) to every 'row' or 'record' in the mas_no field and return a 'false / 1' in H2 only if the value in D2 does not appear anywhere in column G.

Re: Excel MATCH function in Qlikview

swuehl — Tue, 03 Nov 2015 23:25:14 GMT

CHECK:

LOAD mas_no as CheckField

FROM YourSource.xls...;

NA:

LOAD long_no,

formula,

job_no,

Master,

mas_no

FROM YourSource.xls...

WHERE NOT EXISTS(CheckField, formula);

DROP TABLE CHECK;

Re: Excel MATCH function in Qlikview

Anonymous — Wed, 04 Nov 2015 22:15:04 GMT

This seem to have worked in my script but thank you for your help guys!

Exists(mas_no,formula)