topic Re: Divide the current value by the next one on a row (Script) in QlikView

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

brunolelli87 — Wed, 25 Nov 2020 16:16:04 GMT

Hello guys,

How can I create a Script that divides the current value by the next row's value, as you can see below:

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
GGBR4	26/05/2020	R$ 11,95	0,966828
GGBR4	25/05/2020	R$ 12,36	1,065517

...

ITUB4	29/05/2020	R$ 13,43	0,992609
ITUB4	28/05/2020	R$ 13,53	1,020362
ITUB4	27/05/2020	R$ 13,26	1,109623
ITUB4	26/05/2020	R$ 11,95	0,966828
ITUB4	25/05/2020	R$ 12,36	1,065517

I have lots of INDXs, so, for each INDX, I want to calculate the DIF.

As you can see, I want to create the column DIF that is row #1 / row #2.
Example: 13.43/13.53 = 0.992609

This is the script where you can find my code:

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

How can I do that?
I want to do it by Script

Thank you so much!

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

jwjackso — Sat, 30 May 2020 20:55:51 GMT

Load the table in reverse order and use the Previous function

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

brunolelli87 — Sat, 30 May 2020 21:11:18 GMT

Thanks!

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

Saravanan_Desingh — Sun, 31 May 2020 00:16:57 GMT

One solution is.

tab1: LOAD RowNo() As RowID,* INLINE [ INDX, DVPG, VDIF GGBR4, 29/05/2020, 13.43 GGBR4, 28/05/2020, 13.53 GGBR4, 27/05/2020, 13.26 GGBR4, 26/05/2020, 11.95 ITUB4, 29/05/2020, 13.43 ITUB4, 28/05/2020, 13.53 ITUB4, 27/05/2020, 13.26 ITUB4, 26/05/2020, 11.95 ]; tab2: LOAD RowID, INDX, VDIF As VDIF1 Resident tab1 Where Odd(RowID); Left Join (tab2) LOAD RowID-1 As RowID, INDX, VDIF As VDIF2 Resident tab1 Where Even(RowID); tab3: LOAD INDX, VDIF1/VDIF2 As DIF Resident tab2; Drop Table tab1, tab2;

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

Saravanan_Desingh — Sun, 31 May 2020 00:18:01 GMT