[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

[Chanty4u]

TableName:

load *,

Revenues - Costs as Profits;

LOAD
Costs,
Revenues

FROM $(vDATA)\name.qvd (qvd);

[arnoqlik]

Profits is still zero

[Chanty4u]

if below code is not working .Can you share sample data?

load *,

sum(Revenues) - sum(Costs) as Profits;

LOAD
Costs,
Revenues

FROM $(vDATA)\name.qvd (qvd);

[arnoqlik]

Here you go

[MayilVahanan]

Hi @arnoqlik 

Try like below

If(Len(Trim(Revenues))=0, -Costs, Revenues) as Profit

LOAD *, If(Len(Trim(Revenues))=0, -Costs, Revenues) as Profit INLINE [
Period, UnitProduction, Revenues, Costs
20201101, 723, 9250,
20201101, 723, 29982,
20201101, 723, , 41501525
];

Hope always, revenue and cost are in different entries in ur data base.

[arnoqlik]

Thanks but I have hundreds of thousands of files in my original file so I can hardly insert a Load * Inline 🙂

Revenues and Costs are in the same .qvd but they are 2 separate fields.

[edwin]

is it possible that either of the fields are being pulled in as a string?  i would load them, create two lists if one is left aligned and the other is right aligned then youll know one is a string.  bec this should be a simple  Filed1 - Field2 as Filed3  exercise so it may not be the expression thats the issue it could be the fields.  if one or both of them are strings try this:

https://community.qlik.com/t5/QlikView-Scripting/String-to-number-conversion/td-p/292649

[arnoqlik]

You're right. There's probably something wrong with the data format.

Extract of my file:

[ECART]:
LOAD
	CDF,
	Rub,
	Costs_ReelAlloc_Type,
	%DIM_TIME,
	Num#(Costs_Mois_Reel_Montant,'0.00') as Costs,
	Num#(Costs_Mois_Alloc_Montant,'0.00') as Revenues
FROM $(vDATA_DATACOM)\CostsEcartMois.qvd (qvd);

ECART2:
NOCONCATENATE LOAD
	CDF,
	Rub,
	Costs_ReelAlloc_Type,
	%DIM_TIME,
	Costs,
	Revenues,
	Revenues - Costs as Dif
Resident ECART;
Drop Table ECART;
EXIT SCRIPT;

This is what I get:

[MayilVahanan]

Hi @arnoqlik 

That's for sample .. you can replace inline with ur qvd or source.. 

Logic : 

LOAD *, If(Len(Trim(Revenues))=0, -Costs, Revenues) as Profit

From urqvd;

In front end, u can use

Sum(profit)