topic Re: How to calculate values between the both filed? in QlikView

How to calculate values between the both filed?

Rick21 — Thu, 28 Jul 2022 02:25:45 GMT

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?

Re: How to calculate values between the both filed?

sidhiq91 — Thu, 28 Jul 2022 02:56:19 GMT

@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.

Re: How to calculate values between the both filed?

Rick21 — Thu, 28 Jul 2022 07:10:10 GMT

OK! I got it. Thanks!

Re: How to calculate values between the both filed?

luiferva — Thu, 28 Jul 2022 07:10:39 GMT

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