Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted

Re: Previous Balance and Final Inventory Balance

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

Highlighted

Re: Previous Balance and Final Inventory Balance

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

4 Replies
Highlighted

Re: Previous Balance and Final Inventory Balance

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

Highlighted
Specialist
Specialist

Re: Previous Balance and Final Inventory Balance

😁THANK YOU VERY MUCH! 

Neves
Highlighted
Specialist
Specialist

Re: Previous Balance and Final Inventory Balance

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
Highlighted

Re: Previous Balance and Final Inventory Balance

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