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
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.
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.
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:
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:
That's for sample .. you can replace inline with ur qvd or source..
LOAD *, If(Len(Trim(Revenues))=0, -Costs, Revenues) as Profit
In front end, u can use