topic Re: Strict AND in QlikView

Strict AND

christian77 — Tue, 19 Nov 2013 11:34:22 GMT

Hi Qlikers.

When I create a set and I say Product = {‘A’,’B’}, set analysis brings all clients who have bought Product A OR Product B.

The question is: How can I get those clients who have bought BOTH A and B Products?

In other words, how to set a Strict AND in set analysis?

Thanks.

Re: Strict AND

Anonymous — Tue, 19 Nov 2013 11:57:31 GMT

Christian

You need to use the AND mode in your List Box.

I have pasted the below from one of Stephen Redmond's blog posts on his QlikTips Web site

To enable "AND mode" in a list box, there are a couple of rules that we need to follow:

1. The field in the list box must be contained in a table that has only 2 fields.

2. The other field in the table is the association (or ID) field. This field is only contained in this table.

3. The table must have been loaded with the "DISTINCT" keyword.

Best Regards, Bill

Re: Strict AND

jonathandienst — Tue, 19 Nov 2013 12:05:56 GMT

Use the intersection operator in the set expression; something like

<Product={'A'}>*<Product={'B'}>

<Customer=P(<Product={'A'}>}>*<Customer=P(<Product={'B'}>}>

Regards

Jonathan

Re: Strict AND

christian77 — Tue, 19 Nov 2013 12:41:37 GMT

Hi Bill.

Thanks for your answer.

My model does not accomplish those initial conditions. I can’t do it that way.

We should be able to do that with set analysis.

Re: Re: Strict AND

christian77 — Tue, 19 Nov 2013 13:49:47 GMT

Hi Jonathan:

I've tried your way. I’m not familiar with P and E sets since I don’t usually use them.

I’ve got the desired result for 3 product selection but it’s got to be dynamic since I don’t know how many Products are going to be selected at a time.

I already have a try to dynamize it, (not dynamite it), but still does not work.

Anyway, the big issue here is.

When are we going to have a set analysis with set intersection within the same field?

Or…

When QlikTech is going to simplify our clients and ourselves life in this matter?

Thank you for your answer.

This isn’t over.

Re: Re: Strict AND

Anonymous — Tue, 19 Nov 2013 13:55:47 GMT

Christian

Please forgive me for daring to say this, but you could sort your data model and use the AND mode in your List Box.

Best Regards, Bill

Re: Strict AND

christian77 — Tue, 19 Nov 2013 14:05:51 GMT

No problem, you can advise whatever you want. There’s freedom of expression. I’m the one asking for advice.

The one that I posted is just an example. Here is the real one. Product appears bold.

Of course I’d like to do that, that’d be the easiest way. No syntax, no headache.

Maybe extracting Product and Product_id from Product table like a select distinct Product_id resident Products?

Re: Strict AND

CELAMBARASAN — Tue, 19 Nov 2013 14:19:28 GMT

You could try something like this

Sum($(='{<Cliente=P({<Producto={' & Concat(DISTINCT Producto, '}>} Cliente) * P({<Producto={') & '}>} Cliente)>}') Valor)

Re: Strict AND

markmccoid — Tue, 19 Nov 2013 15:55:51 GMT

Since I too do not use the P or E sets too often, I would solve this the following way:

Sum ({<Cliente={"=COUNT({<Producto={'P001'}>} Cliente)>0"}>

<Cliente={"=COUNT({<Producto={'P004'}>} Cliente)>0"}>} Valor)

Using the intersection of the Clients that have each product.

It sounds like you want to have this be dynamic based on the selection in the Producto field. If that is the case you could put the following in a variable:

='SUM({' & Concat(Distinct {<Producto=Producto>} '<Cliente={"=COUNT({<Producto={' & chr(39) & Producto, chr(39) & '}>} Cliente)>0"}>*') & chr(39) & '}>} Cliente)>0"}>} Valor)'

Re: Strict AND

Anonymous — Tue, 19 Nov 2013 15:58:05 GMT

Christian

Yup, something like you said above should help :

select distinct

Product_id ,

Producto as List_Producto \\ rename it to avoid duplicate field name, or maybe drop Producto from source table

resident Products;

Best Regards, Bill