Previous Balance and Final Inventory Balance

[Ribeiro]

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

[rubenmarin · Accepted solution]

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;

[Ribeiro]

😁THANK YOU VERY MUCH! 

[Ribeiro]

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

[rubenmarin · Accepted solution]

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;

[Ribeiro]

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