topic Set analysis question in QlikView

Set analysis question

jduluc12 — Sun, 01 Oct 2017 15:13:46 GMT

Hi,

I got this data set

Cust product Amount

A Prod_1 10

A Prod_2 20

B Prod_1 20

C Prod_1 30

C Prod_3 10

I need to write an expression where it sums the amount for only that customer which has only Prod_1.

For Example

It should consider only Cust B because both A and C have others product too .

Thanks,

Re: Set analysis question

vishsaggi — Sun, 01 Oct 2017 15:45:12 GMT

What is your expected output? You want to see only B cust in your table?

Re: Set analysis question

jduluc12 — Sun, 01 Oct 2017 15:56:05 GMT

Yes that is right

Re: Set analysis question

its_anandrjs — Sun, 01 Oct 2017 15:56:30 GMT

In expression write

=count({<Cust={"=count(distinct product)=1"}>}Distinct Amount)

Re: Set analysis question

its_anandrjs — Sun, 01 Oct 2017 15:59:03 GMT

Sorry this is right one try

=Sum({<Cust={"=count(distinct product)=1"}>}Distinct Amount)

Re: Set analysis question

its_anandrjs — Sun, 01 Oct 2017 16:11:49 GMT

Or even this is right one

=Sum({<Cust={"=count(distinct product)=1"}>} Amount)

Dear Jean do you have confusion on the output that shown to you let me know about this.

Re: Set analysis question

antoniotiman — Sun, 01 Oct 2017 17:54:02 GMT

Hi Jean,

may be this

Sum({<Cust=E({<product-={Prod_1}>})>} Amount)

Regards,

Antonio

Re: Set analysis question

its_anandrjs — Sun, 01 Oct 2017 18:21:21 GMT

Another way is you can achieve the same in the script part by find out the frequency of the product count.

Source:

LOAD * INLINE [

Cust, product, Amount

A, Prod_1, 10

A, Prod_2, 20

B, Prod_1, 20

C, Prod_1, 30

C, Prod_3, 10

];

ProdFrq:

Left Join

LOAD

Cust,

Count(product) as CountProd

Resident Source

Group By Cust;

NoConcatenate

LOAD * Resident Source Where CountProd = 1;

DROP Table Source;

Re: Set analysis question

jduluc12 — Mon, 02 Oct 2017 01:10:52 GMT

It is not working

Re: Set analysis question

its_anandrjs — Mon, 02 Oct 2017 05:29:58 GMT

Can you share some sample for this or can you check my sample on top on that it is working.

Re: Set analysis question

jduluc12 — Mon, 02 Oct 2017 11:16:20 GMT

I checked the sample and it was not working.

i am not sure if it is Qlikview version issue.

Re: Set analysis question

sunny_talwar — Mon, 02 Oct 2017 11:57:03 GMT

May be this

Sum({<Cust = {"=product = 'Prod_1' "}>}Amount)