How to calculate values between the both filed?

[Rick21]

Hello There,

I want to calculate the inflow and outflow.

Sample data source:

Should output:

I try to do that:

if(B=A, Buy-Sell, Buy), but not working.

Could somebody share with me how to do that?

[sidhiq91 · Accepted solution]

@Rick21  Please use the below code to get the desired output. I have also attached the output Screen shot.

NoConcatenate
Buy:
Load * Inline [
Fruit, Buy
Apple,10
Cherry,12
Grape,22
Guava,10
Pear,15
];

Outer join (Buy)
Sell:
Load * Inline [
Fruit, Sell
Apple,8
Cherry,20
Orrange,10
];

NoConcatenate
Main:
Load Fruit,
Buy-Sell as Ouptut;

Load Fruit,
if(isnull(Buy),0,Buy) as Buy,
if(isnull(Sell),0,Sell) as Sell

Resident Buy;

Drop table Buy;

Exit Script;

Please let me know in case of any concerns. If this resolves your issue please like and accept it as a solution.

[Rick21]

OK! I got it. Thanks!

[luiferva]

Hola Rick21

Realize lo siguiente para obtener el resultado de tu imagen

Inserte los datos de tu imagen a un excel y luego lo cargue a Qlik

Despues utilize el siguiente script

Buy:
LOAD
     A as %Product,
     A, 
     Buy
FROM
[..\..\..\coalesce.xlsx]
(ooxml, embedded labels, table is Hoja2);

Outer join(Buy)

Sell:
LOAD 
	B as %Product,
	 B, 
     Sell
FROM
[..\..\..\coalesce.xlsx]
(ooxml, embedded labels, table is Hoja3);

Inventory:
Load *, Coalesce(Buy -Sell,Sell*-1,Buy) as Inventory Resident Buy;
drop table Buy;

Adjunto imagen con el resultado

Si logre ayudarte por favor regalame un like para saberlo, estoy atento a tu revisión, Feliz dia