Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
brunolelli87
Creator II
Creator II

Show a different value on a ListBox

Hello,

I'm using the following scrip to collect some data from Yahoo website.

For each Index in 'BRL=X', 'EURBRL=X', 'CL=F', 'GC=F', 'SI=F', 'BTC-USD'
	
	Indices:
	Replace 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=1432252800&period2=1590105600&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, on a ListBox, I'm showing the values of INDX for the user's selection...

 

But instead of showing 'BRL=X', 'EURBRL=X', 'CL=F', 'GC=F', 'SI=F', 'BTC-USD', I would like to show 'Cotacao USD', 'Cotacao Euro', 'Petroleo', 'Ouro', 'Prata' and 'BitCoin'

How can I do it?

1 Solution

Accepted Solutions
jonathandienst

The inline load for the mapping table was missing a comma:

 

Labels:
Mapping LOAD * Inline
[
Index, Label
BRL=X, Cotacao USD
EURBRL=X, Cotacao Euro
CL=F, Petroleo
GC=F, Ouro
SI=F, Prata
BTC-USD, BitCoin
];

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

8 Replies
jonathandienst

Use a mapping like this:

MAP_LABELS:
Mapping LOAD * Inline
[
	Index, Label
	BRL=X,		Cotacao USD
	EURBRL=X,	Cotacao Euro
	CL=F,   	Petroleo
	GC=F,    	Ouro
	SI=F,    	Prata
	BTC-USD  	BitCoin
];
	

 

Then modify the load of the main table slightly:

Indices:
Replace Load
    Date(Date#(@1,'YYYY-MM-DD'), 'DD/MM/YYYY')	 				as DVPG,	// Data a ser considerada						 
    '$(Index)' 													as INDX,	// Índice
    ApplyMap('MAP_LABELS', '$(Index)')							as LABEL,   // New label field
    Num#(@2, '#,##0,00;-#,##0.00', '.', ',')					as VDIA, 	
...

 

Now use the LABEL field.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
zhadrakas
Specialist II
Specialist II

fast and dirty:

 

if('$(Index)'='BRL=X',  'Cotacao USD',
if('$(Index)'='EURBRL=X',  'Cotacao Euro',
if('$(Index)'='CL=F',  'Petroleo',
...))) as Index_TXT

 

better would be a mapping table that you can join to your fact table.

IndexIndex_TXT
BRL=XCotacao USD
EURBRL=XCotacao Euro

 

brunolelli87
Creator II
Creator II
Author

Hello,

Thanks for your help...

It did not work very well, please take a look on what I did:

Labels:
Mapping LOAD * Inline
[
	Index, Label
	BRL=X,		Cotacao USD
	EURBRL=X,	Cotacao Euro
	CL=F,   	Petroleo
	GC=F,    	Ouro
	SI=F,    	Prata
	BTC-USD  	BitCoin
];


For each Index in 'BRL=X', 'EURBRL=X', 'CL=F', 'GC=F', 'SI=F', 'BTC-USD'
	
	Indices:
	Replace Load
		Date(Date#(@1,'YYYY-MM-DD'), 'DD/MM/YYYY')	 				as DVPG,	// Data a ser considerada						 
		'$(Index)' 													as INDX,	// Índice
		ApplyMap('Labels', '$(Index)')								as LABEL,   // New label field
	    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=1432252800&period2=1590105600&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

 

The result, I got a ListBox with just One field, the last one, 

Result.png

How can I solve it?

Thanks

zhadrakas
Specialist II
Specialist II

tried this and it works for me.

Labels:
Load 'BRL=X' as Index, 		'Cotacao USD' as Label AutoGenerate(1);
Load 'EURBRL=X' as Index, 	'Cotacao Euro' as Label AutoGenerate(1);
Load 'CL=F' as Index, 		'Petroleo' as Label AutoGenerate(1);
Load 'GC=F' as Index, 		'Ouro' as Label AutoGenerate(1);
Load 'SI=F' as Index, 		'Prata' as Label AutoGenerate(1);
Load 'BTC-USD' as Index, 	'BitCoin' as Label AutoGenerate(1);

Map_Labels:
Mapping Load * Resident Labels;
drop table Labels;

you can also put a default value in the applymap statement to check if there is no match.

ApplyMap('Map_Labels', '$(Index)', 'NO_MATCH')
brunolelli87
Creator II
Creator II
Author

I believe I'm doing something wrong, becuase it's not working again!

Look at that:

Teste.png

 

Maybe it's because I'm doing a For Loop...
Should I insert your code inside the For Loop?


I'll take a look at it!

brunolelli87
Creator II
Creator II
Author

Nop!

Look at that:

Teste2.png

jonathandienst

The inline load for the mapping table was missing a comma:

 

Labels:
Mapping LOAD * Inline
[
Index, Label
BRL=X, Cotacao USD
EURBRL=X, Cotacao Euro
CL=F, Petroleo
GC=F, Ouro
SI=F, Prata
BTC-USD, BitCoin
];

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

brunolelli87
Creator II
Creator II
Author

Great!

It's working now,

Thanks