topic How to use the Match function? in QlikView

How to use the Match function?

Tue, 25 Feb 2014 09:55:46 GMT

Hi, I am trying to get the Match function to work, since I think it is a good way to solve my problem (if not, let me know!) I have a excelsheet like the one attached and tried with the expression as follows:

=if(Match(NBR, Nbr)>0,sum(Sales),0)

My desired answer is the sum of sales for customer ABDF with matching number i.e 206, how can I get this to work?

Thanks in advance.

/Lina

Re: How to use the Match function?

alexandros17 — Tue, 25 Feb 2014 09:58:03 GMT

=if(Match(NBR, 'Nbr')>0,sum(Sales),0)

Simply Add ' char

Re: How to use the Match function?

MK_QSL — Tue, 25 Feb 2014 10:05:30 GMT

Something Like Below....

=if(Match([Customer Name],'ABC','DEF'), SUM(Sales))

=if(WildMatch([Customer Name],'*B*','*E*'), SUM(Sales))

Re: How to use the Match function?

Tue, 25 Feb 2014 10:06:33 GMT

Thanks Alessandro, I got this to work if I write e.g 'YYY999', but not for the whole Nbr column. Maybe there is anothe function more suitable. The problem I want to solve is to know if the NBR coumn contains numbers that also is present in the Nbr column, and if there is a match I want to summarize the sales data for the one thet matches. Understandable?

Re: How to use the Match function?

alexandros17 — Tue, 25 Feb 2014 10:09:12 GMT

Use WildMatch, look in the help section in Qlik

Then you can do something like:

Sum(if(wildmatch...), myVal, 0)

This will return the sum of elements that match

Re: How to use the Match function?

maxgro — Tue, 25 Feb 2014 10:12:53 GMT

dimension Customer

expression sum(if(NBR=Nbr,Sales))

DIRECTORY;

LOAD Customer,

Nbr

FROM

TestMatch.xlsx

(ooxml, embedded labels, table is Sheet1);

LOAD Customer,

NBR,

Sales

FROM

TestMatch.xlsx

(ooxml, embedded labels, table is Sheet2)

Re: How to use the Match function?

MK_QSL — Tue, 25 Feb 2014 10:13:03 GMT

=If(Match(NBR, 'Nbr')>0 or isNUM(NBR), SUM(Sales),0)