topic Intersection of two listboxes in QlikView

Intersection of two listboxes

Wed, 02 Feb 2011 09:11:08 GMT

Hello,

i have two listboxes which show a list of client ids:

Listbox 1

If ((Event= 1) and Timestamp((Heute -1) + '12:00:00') < Zeitpunkt and Zeitpunkt <= Timestamp((Heute) + '12:00:00'), ClientID)

Listbox 2

If ((Event= 1) and Timestamp((Heute -2) + '12:00:00') < Zeitpunkt and Zeitpunkt <= Timestamp((Heute -1) + '12:00:00'), ClientID)

Is there any possibility to create a new listbox with intersection of both lists above (Listbox 1 and Listbox 2)?

Intersection of two listboxes

matt_crowther — Wed, 02 Feb 2011 10:38:42 GMT

I may be making this overly simplistic or missing your point but you can use the 'or' & 'and' operators to join 2 if statements eg:

=if(left(SalesPerson,1)='H' or left(SalesPerson,1)='W',SalesPerson)

Hope that helps,

Matt - Visual Analytics Ltd

=if(left(SalesPerson,1)='H' and right(SalesPerson,1)='m',SalesPerson)

Intersection of two listboxes

Wed, 02 Feb 2011 11:11:20 GMT

Thanks,

i implemented your suggestion:

if(((Event= 1) and Timestamp(Heute -1 + '12:00:00')< Zeitpunkt and Zeitpunkt <= Timestamp(Heute + '12:00:00'))

and

((

Event= 1) and Timestamp(Heute -2 + '12:00:00')< Zeitpunkt and Zeitpunkt <= Timestamp(Heute-1 + '12:00:00')), ClientID )

This statement (wth the operator "and") has no result. With the operator "or" i get the list of both conditions (union). But we need the intersection of both conditions.

Intersection of two listboxes

matt_crowther — Wed, 02 Feb 2011 11:50:34 GMT

Again; I may be missing something but the 2 conditions you're trying to join conflict with each other in that no single 'Zeitpunkt' value can meet both conditions and therefore be returned.

Lets say 'Heute' = 10:00am

10 - 1 + 12 = 9:00pm / 10 + 12 = 10:00pm so if Zeitpunkt = 9:45pm it meets the condition so we take it to the second expression:

10 - 2 + 12 = 8:00pm / 10 - 1 + 12 = 9:00pm so our 9:45pm Zeitpunkt value fails to meet the condition and therefore isn't returned.

Thats why no values are returned using the 'and' operator but I think I understand what you need; ClientID's that have had and 'entry' in both conditions not either or and not both conditions simultaneously...that's trickier.

The inherent issue is that you need an 'if' to narrow down the results but an 'if' works on a line by line basis and no single line can meet both conditions. As a work around I'd look to make a macro or action that automatically selects the values in the 2 seperate listboxes.

I'll keep thinking as it's a fundamental issue that I'm sure has a solution.

All the best,

Matt - Visual Analytics Ltd

Intersection of two listboxes

matt_crowther — Wed, 02 Feb 2011 12:07:41 GMT

Right I think we're there with aggregated set analysis:

Listbox expression:

=if(aggr(count({1<Product_Class= {'Pet Food'} >} SalesPerson),SalesPerson)>=1 and aggr(count({1<Product_Class= {'Ready Meals'} >} SalesPerson),SalesPerson)>=1,SalesPerson)

This returns a list of 'SalesPerson'(s) where they have a sale of 'Pet Food' AND a sale of 'Ready Meals' - but not necessarily on the same line. I've tested against making the selections manually in the 'Product_Class' dimension and it returns the same results - ie: those who appear in both listboxes.

You should be able to adapt accordingly to work with your timestamps.

Hope that helps,

Matt - Visual Analytics Ltd

Intersection of two listboxes

Wed, 02 Feb 2011 15:01:39 GMT

Thank you very much Matt,

the key word to solve my problem is the function aggr().