Filter on group and display all rows if at least one row matches filter on filter pane.

[mcemerden]

Hi everyone. So I've been working on Qlik pretty recently. I've got a question that I couldn't figure out myself for hours. 

So I have two tables regarding grocery store transactions.  One table (Transactions) has one row per each transaction and lists information on each transaction such as TransactionID (Key), Date, amount spent, payment method etc. 

The other table (Items) has a row for every single item involved in a transaction. It involves information like, TransactionID (Key), Price, Category,  Description, which aisle the product is in etc. So if someone goes in the grocery store and buys 5 items, there will be 5 rows in the Items table whereas there will only be one row in the Transactions table. 

I want to filter the items table grouped by Transaction ID. So for example, if I want to filter for "Home Goods" among category, the items table will only return all rows that have the value "Home Goods" in the Category column. However, the result I want is returning every item in all transactions that involve at least one "Home Goods" item. 

For example, Transactions and Items tables below:

TransactionID	TransactionDate	AmountSpent
001	09.27.2021	72
002	09.27.2021	150
003	09.26.2021	40

TransactionID	Category	Price
001	Home Goods	35
001	Frozen	12
001	Hygiene	25
002	Outdoors	150
003	Fresh	10
003	Home Goods	15
003	Alcohol	15

So let's say I put a filter pane with Dimension "Category". Then I select, "Home Goods". Applying this filter to both tables gives me:

TransactionID	TransactionDate	AmountSpent
001	09.27.2021	72
003	09.26.2021	40

Which is what I want from the Transactions table. However this is what I get from the Items table:

TransactionID	Category	Price
001	Home Goods	35
003	Home Goods	15

What I actually want is a table that contains all items from transaction 001 because it included a product from that category. The output I want looks like this:

TransactionID	Category	Price
001	Home Goods	35
001	Frozen	12
001	Hygiene	25
003	Fresh	10
003	Home Goods	15
003	Alcohol	15

Is there a way I can achieve this by using a different expression on the filter pane? Thank you!

[Vegar]

Take a look at implicit set analysis,  using P().

https://help.qlik.com/en-US/qlikview/May2021/Subsystems/Client/Content/QV_QlikView/ChartFunctions/Se...

[mcemerden]

I'll check them out now

[anthonyj]

Hi @mcemerden ,

I've been looking at this problem using the possible functions but I've been coming up against the fact that if you choose a category from the column then it will automatically filter out the other categories. I've played with alternate states but haven't been able to get that to work either. I'm sure someone more adept than I can figure that out but in the mean time I worked it out by altering the model.

By adding another table with TransactionID and a Category you can select a Category (Home Goods) and because it is associating through the TransactionID the table will show all Categories associated with that selected Category.

I hope this helps.

Thanks

Anthony

[Vegar]

@mcemerden 

Using the expression below will enable you to get the prices for all transactions for  orders containing any of the selected categories, like in the image above.

=only({$<TransactionID=P(TransactionID), Category>}Price)

[anthonyj]

Great response @Vegar . I tried something similar with the "Only" and P( ) but was slightly off with the logic. I'll file that away for use in the future.

Thanks

Anthony