topic Re: Substract 2 fields in a loading script in QlikView

[SOLVED] Substract 2 fields in a loading script

arnoqlik — Fri, 15 Nov 2024 23:35:56 GMT

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

Re: Substract 2 fields in a loading script

Chanty4u — Thu, 19 Nov 2020 15:30:08 GMT

TableName:

load *,

Revenues - Costs as Profits;

LOAD
Costs,
Revenues

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

Re: Substract 2 fields in a loading script

arnoqlik — Thu, 19 Nov 2020 16:00:16 GMT

Profits is still zero

Re: Substract 2 fields in a loading script

Chanty4u — Thu, 19 Nov 2020 16:08:34 GMT

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);

Re: Substract 2 fields in a loading script

arnoqlik — Fri, 20 Nov 2020 07:53:44 GMT

Here you go

Re: Substract 2 fields in a loading script

MayilVahanan — Fri, 20 Nov 2020 09:02:27 GMT

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.

Re: Substract 2 fields in a loading script

arnoqlik — Fri, 20 Nov 2020 13:44:28 GMT

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.

Re: Substract 2 fields in a loading script

edwin — Fri, 20 Nov 2020 15:12:12 GMT

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

Re: Substract 2 fields in a loading script

arnoqlik — Fri, 20 Nov 2020 15:44:42 GMT

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:

Re: Substract 2 fields in a loading script

MayilVahanan — Fri, 20 Nov 2020 16:04:05 GMT

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)

Re: Substract 2 fields in a loading script

QFabian — Fri, 20 Nov 2020 22:32:20 GMT

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);

Re: Substract 2 fields in a loading script

edwin — Sat, 21 Nov 2020 22:20:54 GMT

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?

Re: Substract 2 fields in a loading script

arnoqlik — Mon, 23 Nov 2020 08:31:04 GMT

@MayilVahanan @QFabian

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

Thank you again.