Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
TableName:
load *,
Revenues - Costs as Profits;
LOAD
Costs,
Revenues
FROM $(vDATA)\name.qvd (qvd);
Profits is still zero
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);
Here you go
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.
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:
https://community.qlik.com/t5/QlikView-Scripting/String-to-number-conversion/td-p/292649
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:
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)