Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

[SOLVED] Substract 2 fields in a loading script

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

Labels (2)
12 Replies
Highlighted
Champion III
Champion III

TableName:

load *,

Revenues - Costs as Profits;

LOAD
Costs,
Revenues

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

Highlighted
Contributor II
Contributor II

Profits is still zero

Highlighted
Champion III
Champion III

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

Highlighted
Contributor II
Contributor II

Here you go

Highlighted

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.

MayilVahanan_0-1605862910660.png

 

Thanks & Regards,
Mayil Vahanan R
Highlighted
Contributor II
Contributor II

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.

Highlighted
Creator III
Creator III

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

 

Highlighted
Contributor II
Contributor II

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:

dif.png

 

Highlighted

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)

Thanks & Regards,
Mayil Vahanan R