topic Re: Previous Balance and Final Inventory Balance in QlikView

Previous Balance and Final Inventory Balance

Ribeiro — Fri, 31 Jul 2020 23:56:58 GMT

Please, I would like to create a solution for loading data. Previous Balance and Current Balance. Separate by Type COM and VEF.

Table:

LOAD * INLINE [
Filial, Produto, Date, Tipo, Seq, Quantidade
51,161642,03/05/2019,COM, 37620,12

51,161642,14/08/2019,COM,38376,24

51,161642,24/08/2019,EVF,38445,4

51,161642,27/08/2019,EVF,38466,2

];

Desired result:

FILIAL	Produto	Date	Tipo	Seq.	Previous	Qtde_COM	Qtde_EVF	Balance
51	161642	03/05/19	COM	37620	0	12	0	12
51	161642	14/08/19	COM	38376	12	12	0	24
51	161642	24/08/19	EVF	38445	24	0	4	20
51	161642	27/08/19	EVF	38466	20	0	2	18

Re: Previous Balance and Final Inventory Balance

rubenmarin — Sat, 01 Aug 2020 10:42:32 GMT

Hi, I have some doubts on how it should work with more data but check the attachment, at least it returns the expected table.

OriginalData: LOAD * INLINE [ Filial ,Produto,Date ,Tipo ,Seq ,Quantidade 51 ,161642 ,03/05/2019 ,COM ,37620 ,12 51 ,161642 ,14/08/2019 ,COM ,38376 ,24 51 ,161642 ,24/08/2019 ,EVF ,38445 ,4 51 ,161642 ,27/08/2019 ,EVF ,38466 ,2 ]; ProcessedData: LOAD *, If(isAccum ,If(Tipo='COM' ,Peek('Balance') + Qtde_COM ,Peek('Balance') - Qtde_EVF) ,If(Tipo='COM', Qtde_COM, -Qtde_EVF) ) as Balance, If(isAccum ,Peek(Balance) ,0) as Previous ; LOAD *, If(Tipo='COM', Quantidade, Alt(Peek('LastCOM'),0)) as LastCOM, If(Tipo='EVF', Quantidade, Alt(Peek('LastEVF'),0)) as LastEVF, If(Tipo='COM', Fabs(If(isAccum, Quantidade-Peek('LastCOM'),Quantidade)), 0) as Qtde_COM, If(Tipo='EVF', Fabs(If(isAccum, Peek('LastEVF')-Quantidade,Quantidade)), 0) as Qtde_EVF ; LOAD Filial, Produto, Date, Tipo, Seq, Quantidade, If(Peek(Filial)=Filial and Peek(Produto)=Produto, 1, 0) as isAccum Resident OriginalData Order By Filial, Produto, Date ; DROP Table OriginalData;

Re: Previous Balance and Final Inventory Balance

Ribeiro — Sat, 01 Aug 2020 12:51:33 GMT

😁THANK YOU VERY MUCH!

Re: Previous Balance and Final Inventory Balance

Ribeiro — Sat, 01 Aug 2020 13:46:23 GMT

Ops!
Something went wrong, I was wrong the amount was 12 not 24. and the COM was 0

OriginalData:
LOAD * INLINE [
Filial ,Produto,Date ,Tipo ,Seq ,Quantidade
51 ,161642 ,03/05/2019 ,COM ,37620 ,12
51 ,161642 ,14/08/2019 ,COM ,38376 ,12
51 ,161642 ,24/08/2019 ,EVF ,38445 ,4
51 ,161642 ,27/08/2019 ,EVF ,38466 ,2
];

Re: Previous Balance and Final Inventory Balance

rubenmarin — Sun, 02 Aug 2020 08:32:32 GMT

I though quantidade was alredy accumulated and that was the tricky part. If it's not alreadu accumulated it will be simpler, not need to store the 'Last*' value

ProcessedData: LOAD *, If(isAccum ,If(Tipo='COM' ,Peek('Balance') + Qtde_COM ,Peek('Balance') - Qtde_EVF) ,If(Tipo='COM', Qtde_COM, -Qtde_EVF) ) as Balance, If(isAccum ,Peek(Balance) ,0) as Previous ; LOAD *, If(Tipo='COM',Quantidade, 0) as Qtde_COM, If(Tipo='EVF', Quantidade, 0) as Qtde_EVF ; LOAD Filial, Produto, Date, Tipo, Seq, Quantidade, If(Peek(Filial)=Filial and Peek(Produto)=Produto, 1, 0) as isAccum Resident OriginalData Order By Filial, Produto, Date ; DROP Table OriginalData;

Re: Previous Balance and Final Inventory Balance

Ribeiro — Sun, 20 Feb 2022 14:07:01 GMT

I'm not getting your perfect solution. Got any tips?



I need to create opening balance and ending balance, by date_time, product.

Table OriginalData;

LOAD * INLINE [
DATA_HORA,Filial,Produto,Quantidade,Tipo,
19/02/2022 12:59:27,156,212,150,S
19/02/2022 12:56:22,156,212,75,E
18/02/2022 04:44:13,156,212,75,S
18/02/2022 04:35:19,156,212,27,E

];

Desired result:

FILIAL	Produto	DATA_HORA	Tipo	Previous	E	S	Balance
156	212	18/02/2022 04:35:19	E	0	27	0	27
156	212	18/02/2022 04:44:13	S	27	0	75	-48
156	212	19/02/2022 12:56:22	E	-48	75	0	27
156	212	19/02/2022 12:59:27	S	27	0	150	-123

Order By

Produto,

DATA_HORA,

Filial

DROP Table OriginalData;

Link Post:

https://community.qlik.com/t5/QlikView-App-Dev/previous-balance-inflows-and-outflows-and-closing-balance/m-p/1895130#M1217489