Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Ribeiro
Specialist
Specialist

Previous Balance and Final Inventory Balance

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:

FILIALProdutoDateTipoSeq.PreviousQtde_COMQtde_EVFBalance
5116164203/05/19COM37620012012
5116164214/08/19COM383761212024
5116164224/08/19EVF38445240420
5116164227/08/19EVF38466200218




Neves
2 Solutions

Accepted Solutions
rubenmarin

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;

View solution in original post

rubenmarin

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;

View solution in original post

5 Replies
rubenmarin

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
Specialist
Specialist
Author

😁THANK YOU VERY MUCH! 

Neves
Ribeiro
Specialist
Specialist
Author

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

2020-08-01_10-42-29.jpg

Neves
rubenmarin

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
Specialist
Specialist
Author

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

Neves