topic Re: sum if in QlikView

sum if

Fri, 19 Jul 2013 10:58:57 GMT

CCY	Amount
INR	-200
INR	-250
EUR	0
USD	400
USD	-300
USD	-150

Total Amount	Total Amount in USD
-500	-50

I want to use sum if but my logic seems to be not working - can you help?

My script:

LOAD

sum(Amount) as Total,

if(CCY = 'USD', sum(Amount)) as TotalUSD

Resident Table;

The if condition does not return any value.

Re: sum if

Fri, 19 Jul 2013 11:01:55 GMT

Try

Sum( if(CCY = 'USD', Amount)) as TotalUSD

Re: sum if

Fri, 19 Jul 2013 11:16:09 GMT

Still does not work -> getting no output for TotalUSD

Re: sum if

narender123 — Fri, 19 Jul 2013 11:32:05 GMT

HI ,

use this

if(CCY='USD',Amount) as TotalUSD

As you are using aggrgation function the use group by too.

like.

load

sum(Amount) as Total,

if(CCY='USD',Amount) as TotalUSD

from tableabc group by a,b;

Thanks.

Re: sum if

jagan — Fri, 19 Jul 2013 11:37:34 GMT

Hi,

Try like this

DataTemp:

LOAD

Amount

if(CCY = 'USD', Amount, 0) as TotalUSD

Resident Table;

Data:

LOAD
Sum(Amount) AS Amount,

Sum(TotalUSD) AS TotalUSD

RESIDENT DataTemp;

DROP TABLE DataTemp;

Hope this helps you.

Regards,

Jagan.

Re: sum if

omyahamburg — Fri, 19 Jul 2013 11:46:37 GMT

A straight table with no Dim and these expressions will also do it:

=sum(Amount)

=sum({<CCY={'USD'}>}Amount)