sum if

[#1]

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.

[#2]

Try

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

[#3]

Still does not work -> getting no output for TotalUSD

[narender123]

HI ,

use this

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

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

like.

load

a,

b,

sum(Amount) as Total,

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

from tableabc group by a,b;

Thanks.

[jagan · Accepted solution]

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.

[omyahamburg]

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

=sum(Amount)

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