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

[brunolelli87]

Hello guys,

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

INDX	DVPG	VDIF	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

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

[michal__ · Accepted solution]

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

[rwunderlich]

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

[brunolelli87]

Thanks for your kindly help.

It's working perfectly!

Have a nice day

[brunolelli87]

Thanks for your help!

Your solution is working as well as the first one!

Thanks