topic Re: Total aggr question in QlikView

Total aggr question

jduluc12 — Sun, 20 May 2018 15:33:37 GMT

Hi,

My data set looks like this

Cust	Addr	Amount
Cust1	addr1	100
Cust1		200
Cust2	addr2	300
Cust2		400

My pivot table should like this

Cust	Addr	Amt with addr	Amt without addr
Cust1	addr1	100	200
Cust2	addr2	300	400

Is it possible without changing the data set?

I tried using TOTAL to ignore Addr

sum(Total<Addr>Amount) for 3rd column but it brings 0 value.

Jean

Re: Total aggr question

shiveshsingh — Sun, 20 May 2018 15:54:41 GMT

You can try 2 expressions for output

if(len(Addr)=0,sum(Amount))

if(len(Addr)<>0,sum(Amount))

Re: Total aggr question

isingh30 — Sun, 20 May 2018 16:01:57 GMT

Shivesh...we have to add zero in the data set first? As of now, it's blank.

Thanks.

Re: Total aggr question

isingh30 — Sun, 20 May 2018 16:12:10 GMT

sum({<Addr = {'addr1','addr2'}>}Amount)

sum({<Amount = {200,400}>}Amount)

I got this.

Thanks

Re: Total aggr question

passionate — Sun, 20 May 2018 16:14:25 GMT

PFA, Solution

Re: Total aggr question

isingh30 — Sun, 20 May 2018 16:19:04 GMT

Can you share your code & output?

Thanks.

Re: Total aggr question

shiveshsingh — Sun, 20 May 2018 16:26:15 GMT

Add zero for Addr? you mean this or something else?

Re: Total aggr question

passionate — Sun, 20 May 2018 16:28:57 GMT

LOAD Cust,

Addr,

len(Addr) as length,

Amount

FROM

[https://community.qlik.com/thread/302015]

(html, codepage is 1252, embedded labels, table is @1);

Re: Total aggr question

isingh30 — Sun, 20 May 2018 16:32:45 GMT

Please check the question again. Your answer is not complete.

Thanks.