topic Re: counting if match unique variables in QlikView

counting if match unique variables

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello! I am very new to the world of qlikview, and would like to set up a chart that counts a row if (and only if) it matches the specific criteria. Please see below for an example (as I'm not sure how else to describe it).

Country Colour Weather

UK Blue Sunny

Uk red sunny

UK Blue sunny

UK Blue Sunny

France Blue Sunny

France Blue Rainy

UK Red Rainy

France Red Sunny

I would now like to create a table that is as follows and counts how many have the same country, color, and weather:

Country colour weather count

UK blue sunny 3

UK red sunny 1

UK blue rainy 0

UK Red rainy 1

France blue sunny 1

France red sunny 1

France blue rainy 1

France red rainy 0

How can I get the count in a chart and ensure that the country, colour, and weather all match? Thank you for your time and help

Re: counting if match unique variables

krishnacbe — Mon, 24 Oct 2016 13:45:08 GMT

Hi,

Can you share the expected output.

Re: counting if match unique variables

Anonymous — Mon, 24 Oct 2016 13:53:53 GMT

combine all the columns data in to one column "combined"

like country&Color&Weather .......

Country Colour Weather Combined

UK Blue Sunny UKBlueSunny

Uk red sunny Ukredsunny

UK Blue sunny ...........

UK Blue Sunny ........

France Blue Sunny .......

France Blue Rainy ........

UK Red Rainy ........

France Red Sunny...........

then use Count(distinct(Combined)) function ...

Re: counting if match unique variables

johnw — Mon, 24 Oct 2016 23:48:27 GMT

If we're allowed to not even display the rows with 0, then it's this simple:

here's nothing special about counting Weather. Any of the three fields would do.

If you really must see the rows that have a count of 0, that's doable but more difficult, because QlikView really hates to how you combinations of data that don't exist in your data model. If you can get away with skipping them, I would.

Re: counting if match unique variables

Anil_Babu_Samineni — Tue, 25 Oct 2016 01:31:30 GMT

Can you please release the business key as your intend. So that, we may can help you. How do you calculating the values of count to get this values

Re: counting if match unique variables

Anonymous — Tue, 25 Oct 2016 08:48:24 GMT

Thank you all!

Alluraiah - if I don't figure anything else out, I'll do that (but I would prefer to not have to create another column, simply to keep things looking nice).

Attached is an example of what I have been working with. I have a bunch of lines, and I would like to be able to sort and count them based on all three criteria. I know how to do it with two criteria, but not with three. Is there a special code that I should put into expression field to make that work?

Re: counting if match unique variables

Anonymous — Tue, 25 Oct 2016 08:53:23 GMT

sorry! here is the example I mentioned in my previous comment!

Re: counting if match unique variables

johnw — Tue, 25 Oct 2016 16:05:46 GMT

And what results do you want to see? Here's the results of the expression I suggested: