topic Re: Count rows where two fields don't match in QlikView

Count rows where two fields don't match

kstroupe — Fri, 09 Feb 2018 04:40:51 GMT

Hello,

I have data where I'm trying to count in a chart where two fields don't match.

Header 1	Header 2	Header 3	Header 4	Header 5	Header 6
1	1	HW	HW	414	414
1	2	HW	SW	414	412

I would like to get a count of 1

Count (Header 1,Header 2) if Header 3 <> Header 4

I can make a flag at the table level and it works but when I try to put it in a Chart it fails.

Thanks

Kim

Re: Count rows where two fields don't match

tresB — Fri, 09 Feb 2018 05:20:00 GMT

Have you tried like:

=Count( if ([Header 3] <> [Header 4], [Header 2]))

Re: Count rows where two fields don't match

kstroupe — Fri, 09 Feb 2018 14:52:25 GMT

Thank you for your response...I did try that and it worked as long as I was only counting Header 1, but I want to count dimension Header 1 and Header 2 concatenated together and it's at that point where my expression fails.

Re: Count rows where two fields don't match

Peter_Cammaert — Fri, 09 Feb 2018 15:03:39 GMT

In Clever's example, you are counting rows of an internal table that have Header3 <> Header4. That's the same as counting concatenated Header1 and Header2 values, unless you want to count distinct concatenated Header1 and Header2 value.

BTW to count occurrences of a composite field, perform a real concatenate like Header1 & Header2

Re: Count rows where two fields don't match

kstroupe — Fri, 09 Feb 2018 15:50:45 GMT

Thank you... I am trying to count distinct because it is giving me a count of 2 where I would like a count of 1. When I try to add the word DISTINCT I have an error in my expression.

=Count (if(Header3 <> Header4, Header1 & Header2))

Re: Count rows where two fields don't match

Peter_Cammaert — Fri, 09 Feb 2018 16:00:25 GMT

Are you asking me where the DISTINCT keyword goes? If so, see here: Count - chart function ‒ QlikView

It goes between the parentheses and in front of the current expression.

Re: Count rows where two fields don't match

kstroupe — Fri, 09 Feb 2018 16:06:51 GMT

Thank you everyone...I solved my issue by adding a count flag to the load which was always 1. and doing it this way.

sum(if(Header3 <> Header4, Aggr(Distinct(QTY),Header1 ,Header2)) )

I appreciate everyone's help and time on this.

Thanks