Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
flygstolen_fred
Creator
Creator

Conditions over multiple rows

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:

OrderIdTotalMisc
1001500Misc1
1002300Misc2

Table Product:

OrderIdProductIdNamePrice
1001175Phone400
1001189Headset100
1002554Charger300
1 Solution

Accepted Solutions
swuehl
MVP
MVP

There were some parenthesis misplaced:

=aggr( Only( {<OrderId = p({<ProductId = {175}>}) * p({<ProductId = {189}>})>} OrderId), OrderId)

View solution in original post

11 Replies
sunny_talwar

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.

marcus_sommer

Try something like this:

sum({< name = {'Phone', 'Headset'}>} Price)

- Marcus

swuehl
MVP
MVP

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:

AND mode in Qlik Sense

hic
Former Employee
Former Employee

This is exactly what "AND-mode" does. See AND-Mode.

An alternative is to do the following:

  1. Select "Phone" in the "Name" list box
  2. Right-click the "OrderId" list box, and choose "Clear other fields". Now you have selected the OrderIDs with a Phone.
  3. Select "Headset" in the "Name" list box. Now you have the combination of the two.

HIC

flygstolen_fred
Creator
Creator
Author

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?

sunny_talwar

Try this in a text box object:

=Concat({<OrderId = p({<ProductId = {175}>}) * p({<ProductId = {189}>})>}OrderId, Chr(10))

flygstolen_fred
Creator
Creator
Author

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.

swuehl
MVP
MVP

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.

flygstolen_fred
Creator
Creator
Author

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.