Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Divide the current value by the next value on a row (Script)

Hello guys,

How can I divide the current value by the next value on a row via Script?
Check the following example:

INDXDVPGVDIFDIF
GGBR429/05/2020R$ 13,430,992609
GGBR428/05/2020R$ 13,531,020362
GGBR427/05/2020R$ 13,261,109623
ITUB429/05/2020R$ 11,950,966828
ITUB428/05/2020R$ 12,361,065517
ITUB427/05/2020R$ 11,600,962656
GOLL429/05/2020R$ 12,051,010906
GOLL428/05/2020R$ 11,921,010169
GOLL427/05/2020R$ 11,800,998308

 

As you can see above, I have some INDXs, and I want to calculate the DIF:
DIF = Current value of VDIF / Next value of VDIF 
Example: 13.43 / 13.53 = 0,992609

Note that I have some INDXs, so this calculus should consider the INDXs... it's not just dates.

 

This is my script:

For each Index in 'BRL=X', 'EURBRL=X', 'CL=F', 'GC=F', 'SI=F', 'BTC-USD', '^BVSP'
	Add
	Indices:
	Load
		Date(Date#(@1,'YYYY-MM-DD'), 'DD/MM/YYYY')	 				as DVPG,	// Data a ser considerada						 
		'$(Index)' 													as INDX,	// Índice
	    Num#(@2, '#,##0,00;-#,##0.00', '.', ',')					as VDIA, 	// Valor do índice na abertura
	    Num#(@3, '#,##0,00;-#,##0.00', '.', ',')					as VMAI, 	// Valor mais alto do índiceno dia
	    Num#(@4, '#,##0,00;-#,##0.00', '.', ',')					as VMBI, 	// Valor mais baixo do índice no dia
	    Num#(@5, '#,##0,00;-#,##0.00', '.', ',')					as VDIF, 	// Valor do índice no fechamento
	    Num#(@6, '#,##0,00;-#,##0.00', '.', ',')					as VIFA, 	// Valor do índice no fechamento ajustado
	    Num#(@7, '#,##0,00;-#,##0.00', '.', ',')					as VDIN		// Volume negociado do índice
	FROM
		[https://query1.finance.yahoo.com/v7/finance/download/$(Index)?period1=1431043200&period2=1593907200&interval=1d&events=history] 	//5 Years Period
		(txt, codepage is 1252, no labels, delimiter is ',', msq, filters(
		Remove(Row, Pos(Top, 1)),
		Remove(Row, RowCnd(CellValue, 2, StrCnd(contain, 'null')))
	));

Next Index

 

And I would like to do it via script
How can I do that?

 

Thanks

1 Solution

Accepted Solutions
Highlighted
Contributor III
Contributor III

Re: Divide the current value by the next value on a row (Script)

Hi,

It is possible with a little help of Previous and Peek. See sample script below.

[DATA_TMP]:
Load
	INDX,
    DVPG,
    SubField(VDAF, ' ', 1)				as [CURR],
    Num(KeepChar(VDAF, '0123456789,')) 	as [VDAF] 
Inline [
INDX|	DVPG|	VDAF|	DIF|
GGBR4|	29/05/2020|	R$ 13,43|	0,992609
GGBR4|	28/05/2020|	R$ 13,53|	1,020362
GGBR4|	27/05/2020|	R$ 13,26|	1,109623
ITUB4|	29/05/2020|	R$ 11,95|	0,966828
ITUB4|	28/05/2020|	R$ 12,36|	1,065517
ITUB4|	27/05/2020|	R$ 11,60|	0,962656
GOLL4|	29/05/2020|	R$ 12,05|	1,010906
GOLL4|	28/05/2020|	R$ 11,92|	1,010169
GOLL4|	27/05/2020|	R$ 11,80|	0,998308
]
(delimiter is '|'
;

NoConcatenate
[DATA_SORTED_TMP]:
Load
	*
Resident
	[DATA_TMP]
Order By
	[INDX] asc,
    [DVPG] asc
;

Drop Table [DATA_TMP];

NoConcatenate
[DATA]:
Load
	*,
    If(Previous([INDX])=[INDX], [VDAF]/Peek([VDAF]), Null()) as  [DIF]
Resident
	[DATA_SORTED_TMP]
;

Drop Table [DATA_SORTED_TMP];

View solution in original post

4 Replies
Highlighted
Contributor III
Contributor III

Re: Divide the current value by the next value on a row (Script)

Hi,

It is possible with a little help of Previous and Peek. See sample script below.

[DATA_TMP]:
Load
	INDX,
    DVPG,
    SubField(VDAF, ' ', 1)				as [CURR],
    Num(KeepChar(VDAF, '0123456789,')) 	as [VDAF] 
Inline [
INDX|	DVPG|	VDAF|	DIF|
GGBR4|	29/05/2020|	R$ 13,43|	0,992609
GGBR4|	28/05/2020|	R$ 13,53|	1,020362
GGBR4|	27/05/2020|	R$ 13,26|	1,109623
ITUB4|	29/05/2020|	R$ 11,95|	0,966828
ITUB4|	28/05/2020|	R$ 12,36|	1,065517
ITUB4|	27/05/2020|	R$ 11,60|	0,962656
GOLL4|	29/05/2020|	R$ 12,05|	1,010906
GOLL4|	28/05/2020|	R$ 11,92|	1,010169
GOLL4|	27/05/2020|	R$ 11,80|	0,998308
]
(delimiter is '|'
;

NoConcatenate
[DATA_SORTED_TMP]:
Load
	*
Resident
	[DATA_TMP]
Order By
	[INDX] asc,
    [DVPG] asc
;

Drop Table [DATA_TMP];

NoConcatenate
[DATA]:
Load
	*,
    If(Previous([INDX])=[INDX], [VDAF]/Peek([VDAF]), Null()) as  [DIF]
Resident
	[DATA_SORTED_TMP]
;

Drop Table [DATA_SORTED_TMP];

View solution in original post

Highlighted
MVP & Luminary
MVP & Luminary

Re: Divide the current value by the next value on a row (Script)

I don't think the intermediate sort is necessary as it's already a Resident table. Using the same previous/peek idea, you can make it a bit more compact by using Join:

Data:
LOAD
     INDX,
    
DVPG,
    
num#(VDIF,'R$ 0,00') as VDIF
FROM
[https://community.qlik.com/t5/New-to-QlikView/Divide-the-current-value-by-the-next-value-on-a-row-Sc...]
(
html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);

Join (Data)
LOAD
  *,
  if(Previous(INDX) = INDX, num(VDIF / Peek('VDIF'), '0,000000'), '') as DIF
Resident Data
Order by INDX, DVPG

;

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

Highlighted
Creator
Creator

Re: Divide the current value by the next value on a row (Script)

Thanks for your kindly help.

It's working perfectly!

 

Have a nice day

Highlighted
Creator
Creator

Re: Divide the current value by the next value on a row (Script)

Thanks for your help!

Your solution is working as well as the first one!


Thanks