Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
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

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

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

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

😁THANK YOU VERY MUCH! 

Neves
Highlighted
Specialist
Specialist

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

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