Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

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

Hello guys,

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

INDXDVPGVDIFDIF
GGBR429/05/2020R$ 13,430,992609
GGBR428/05/2020R$ 13,531,020362
GGBR427/05/2020R$ 13,261,109623
GGBR426/05/2020R$ 11,950,966828
GGBR425/05/2020R$ 12,361,065517

...

ITUB429/05/2020R$ 13,430,992609
ITUB428/05/2020R$ 13,531,020362
ITUB427/05/2020R$ 13,261,109623
ITUB426/05/2020R$ 11,950,966828
ITUB425/05/2020R$ 12,361,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!

1 Solution

Accepted Solutions
Highlighted
Specialist III
Specialist III

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

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;

View solution in original post

4 Replies
Highlighted
Specialist
Specialist

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

Load the table in reverse order and use the Previous function

Tags (1)
Highlighted
Creator II
Creator II

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

Thanks!

Highlighted
Specialist III
Specialist III

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

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;

View solution in original post

Highlighted
Specialist III
Specialist III

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

commQV82.PNG