Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
simonb2013
Creator
Creator

Filtering over multiple rows for a single entity.

My Table : 
Box1 ; Bananas
Box1; Apples
Box1; Mangos
Box2; Bananas
Box2; Oranges
Box2; Plums
Box2; Apricots

User would like to filter and find which box has both Bananas and Plums in it.
I have over 500,000 boxes, some with hundreds of products in them.

Could someone nudge me where to start trying to solve this one ?

Many Thanks

 

 

1 Solution

Accepted Solutions
simonb2013
Creator
Creator
Author

Make a variable : vProductsAvailable = GetPossibleCount(Product)

Then use variable in expression thus : 

=Sum({<[Box] = {"=COUNT([Product])=$(vProductsAvailable)"} >} 1)

View solution in original post

4 Replies
JordyWegman
Partner
Partner

Hi Simon,

Do you have 2 columns?

Then make two filter panes:

  1. Box
  2. Fruit

Let your user select one type of fruit and in the other selection box, all your boxes appear that have the type of fruit selected. You can then also make a table with extra information about the boxes.

Jordy

Climber

Work smarter, not harder
simonb2013
Creator
Creator
Author

That works great for one fruit.

But I want only boxes than contain BOTH : Bananas AND Plums.

If I filter on both Bananas and Plums naturally .. both boxes show.  It is doing an 'OR', where I want an 'AND'

simonb2013
Creator
Creator
Author

Ok, so I have progressed and might be close to a solution.
If I select 2 number Products, then I can use this expression as a measure:

COUNT({<Box = {"=COUNT(PRODUCT)=2"}>} PRODUCT)

Now all I need to do, is to make the filter dynamic.
So, to replace the literal '2' with "GetPossibleCount(PRODUCT)"

Unfortunately I am getting woefully confused how to substitute that.
If anyone could help with the correct syntax and/or explanation that would get the below corrected ?

COUNT({<Box = {"=COUNT(PRODUCT)=$(GetPossibleCount(PRODUCT))"}>} PRODUCT)

simonb2013
Creator
Creator
Author

Make a variable : vProductsAvailable = GetPossibleCount(Product)

Then use variable in expression thus : 

=Sum({<[Box] = {"=COUNT([Product])=$(vProductsAvailable)"} >} 1)

View solution in original post