Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
nlamprecht
Contributor III
Contributor III

counting if match unique variables

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

7 Replies
krishnacbe
Partner
Partner

Hi,

Can you share the expected output.

galax_allu
Specialist
Specialist

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

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

Capture.PNG

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
nlamprecht
Contributor III
Contributor III
Author

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?

nlamprecht
Contributor III
Contributor III
Author

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

johnw
MVP
MVP

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

Capture.PNG