Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

flygstolen_fred
New Contributor III

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

Re: Conditions over multiple rows

There were some parenthesis misplaced:

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

11 Replies

Re: Conditions over multiple rows

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.

Re: Conditions over multiple rows

Try something like this:

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

- Marcus

MVP
MVP

Re: Conditions over multiple rows

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

Highlighted
Employee
Employee

Re: Conditions over multiple rows

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
New Contributor III

Re: Conditions over multiple rows

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?

Re: Conditions over multiple rows

Try this in a text box object:

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

flygstolen_fred
New Contributor III

Re: Conditions over multiple rows

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.

MVP
MVP

Re: Conditions over multiple rows

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
New Contributor III

Re: Conditions over multiple rows

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.

Community Browser