counting if match unique variables

[Anonymous]

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

[krishnacbe]

Hi,

Can you share the expected output.

[Anonymous]

HI

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 ...

[johnw]

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.

[Anil_Babu_Samineni]

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

[Anonymous]

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?

[Anonymous]

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

[johnw]

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