Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have two tables and I wanna have a list of all orders that contain 2 specific products, in this case I wanna have all orders that bought both a phone (175) and a headset (189). Any ideas how to solve this in a easy way? Thanks
Table Order:
OrderId | Total | Misc |
---|---|---|
1001 | 500 | Misc1 |
1002 | 300 | Misc2 |
Table Product:
OrderId | ProductId | Name | Price |
---|---|---|---|
1001 | 175 | Phone | 400 |
1001 | 189 | Headset | 100 |
1002 | 554 | Charger | 300 |
There were some parenthesis misplaced:
=aggr( Only( {<OrderId = p({<ProductId = {175}>}) * p({<ProductId = {189}>})>} OrderId), OrderId)
You can try this expression:
=Sum({<OrderId = p({<ProductId = {175}>}) * p({<ProductId = {189}>})>}Price)
or
=Sum({<OrderId = p({<ProductId = p(ProductId)>})>}Price)
UPDATE: Realizing my mistake based on Stefan's response and fixing it.
Try something like this:
sum({< name = {'Phone', 'Headset'}>} Price)
- Marcus
In QlikView, you could create a data model that allows a so called AND mode list box that should fulfill your requirements.
You can also use a set expression like
{<OrderId = p({<ProductId = {175}>})*p({<ProductId = {189}>}) >}
you can use something like this also in QS, and you can see another approach incl. a demo here:
This is exactly what "AND-mode" does. See AND-Mode.
An alternative is to do the following:
HIC
Hi,
Thanks for all quick replies.
But if I don't want the order sum, I just want a list of all OrderIds that has this combination?
Try this in a text box object:
=Concat({<OrderId = p({<ProductId = {175}>}) * p({<ProductId = {189}>})>}OrderId, Chr(10))
Thanks again Sunny for your reply. Maybe a was a little bit fussy in my explanation. I don't want a string, I wanna fill a "Table" with all Order Ids matching. If I remove the Concat-part it doesnt work.
You can create a list box with t field expression like
=aggr( Only( ({<OrderId = p({<ProductId = {175}>}) * p({<ProductId = {189}>})>} OrderId, OrderId)
or use OrderId in a table chart and any aggregation function with that set expression as expression.
Thanks again for your reply but I must do something totally wrong.
When I click "Add dimension" and paste the "code-snippet" and then connect it to the Table is says Invalid dimension.
Any help is appreciated.