Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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];
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];
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
Thanks for your kindly help.
It's working perfectly!
Have a nice day
Thanks for your help!
Your solution is working as well as the first one!
Thanks