Use SQL "Sum if" in load to split "Amount" to two columns

[Clay]

I'm using SQL in my load statement to bring in data from an Oracle DB using OLEDB

The fact table has a column "AMOUNT" which contains both dollars and headcount. I can separate these from each other based on another field, let's call it "ACCOUNT", where "ACCOUNT" can either be equal to "EXPENSE" or "HEADCOUNT".

I can't find the correct syntax in the load to say:

If ACCOUNT = EXPENSE, sum AMOUNT as "EXPENSE",

If ACCOUNT = HEADCOUNT, sum AMOUNT as "HEADCOUNT"  

Example of current load statement, which works fine, but is not yet splitting the AMOUNT field:

SQL SELECT

ACCOUNT,
Sum (AMOUNT) as Amount

FROM
EDW."FactTable"

WHERE

AMOUNT <> 0

GROUP BY

ACCOUNT

;

Any help getting the right syntax for the sumif would be greatly appreciated, thanks!

[Taoufiq_Zarra]

Maye be :

Load 
ACCOUNT,
if(ACCOUNT='EXPENSE',Amount) as EXPENSE,
if(ACCOUNT='HEADCOUNT',Amount) as HEADCOUNT
;

SQL SELECT

ACCOUNT,
Sum (AMOUNT) as Amount

FROM
EDW."FactTable"

WHERE

AMOUNT <> 0

GROUP BY

ACCOUNT

;

[Brett_Bleess]

If Taoufiq's reply got you what you needed, we would appreciate it if you would return to the thread and close it out by using the Accept as Solution button on his post to give him credit for the help and let other Members know that worked.  

The other option would be to use our Load statement and our script functions to do the work you were trying to do via Oracle SQL statements, as sometimes the Oracle drivers will not process some statements, but the best place to confirm that is going to be on Oracle Community/Support site, as they are the experts on the driver.  Sorry I do not have anything better to offer.

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/Scrip...

And I completely missed the fact Taoufiq did exactly what I was suggesting, my bad! 🙂

Regards,
Brett