[SOLVED] Substract 2 fields in a loading script

[arnoqlik]

Hi everyone,

I'd like to substract 2 fields in my loading script:

TableName:
LOAD
	Costs,
	Revenues,
	Revenues - Costs as Profits,
FROM $(vDATA)\name.qvd (qvd);

I get data/numbers for Costs and Revenues but Profits is always 0.

Thanks in advance for your kind help.

---

EDIT: root cause => null values

Solution 1 or Solution 2

[QFabian]

Hi @arnoqlik , try this, maybe is because of the null values :

TableName:
LOAD
     if(isnull(Costs), 0, Costs) as Costs,
     if(isnull(Revenues), 0, Revenues) as Revenues,
     if(isnull(Revenues), 0, Revenues) - if(isnull(Costs), 0, Costs) as Profits,
FROM $(vDATA)\name.qvd (qvd);

[edwin]

its curious that you would format your number using 0.00 but your number apparently uses comma as decimal point.  also try using num instead of num#

can you create a qvw with data with just the script you posted?

[arnoqlik]

@MayilVahanan  @QFabian 

Both solutions are working. The problem was indeed the null values!

Thank you again.