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

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

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

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

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

michal__ — Sat, 30 May 2020 16:59:36 GMT

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

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

rwunderlich — Sat, 30 May 2020 17:45:19 GMT

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-Script/td-p/1706762]
(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

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

brunolelli87 — Sat, 30 May 2020 19:58:35 GMT

Thanks for your kindly help.

It's working perfectly!

Have a nice day

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

brunolelli87 — Sat, 30 May 2020 20:00:07 GMT

Thanks for your help!

Your solution is working as well as the first one!

Thanks