Convert/Sum criteria

[rbr199]

Hi all.

Please I have a problem (maybe simple to fix) but I'll still learning about QS.

I have a Table with accounting values (Debit and Credit column) but I can't find a Formula (simple or combined) to convert Debit values into negative, it only shows positive at all, i.e.:

SIGNAL	SUM
C	511.99
D	574.16
D	817.40
C	10,777.70

The entire sum must be 9,898.13, considering D as negative, but I cant develop a script to fix it 😞

Any help or suggestion would help me a lot (scrpit, select and so on).

Thanks in advance.

[Aditya_Chitale · Accepted solution]

This can be achieved using a simple if() statement. 

Table1:
Load
     signal,
     if(signal='D', sum*-1, sum) as sum
from [lib://xyz.xlsx];

Result:

Regards,

Aditya

[BrunPierre · Accepted solution]

As an alternative, you could calculate this at the front end.

Sum(If(SIGNAL = 'D', SUM * -1,SUM))

[rbr199]

Thank you all. It partially worked and I still don't know why...

When in the Data Load Editor,  I tried this same IF condition using same critetias (Load, IF Criteria, From and OLE DB) but appears some errors and it can't neither finish nor ending as a Sum case.

When I try just the formulas editor (after ending the load editor properly) at the Value field, the amount isn't getting the total properly, or get the amount of 9,898.13. Here's my screenshot of the same table (ignore some descriptions in portuguese, please).

I know I doing something wrong, but I can't find it 😞

[Or]

It sounds like you may have put the if() statement into an SQL query, rather than into the Load statement? That, or you used an incorrect column name for it? Those seem like the most likely culprits here.

[rbr199]

Thank you, it was a sum function when selectiong the filters in the table.