Show a different value on a ListBox

[brunolelli87]

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?

[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.

[zhadrakas]

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.

Index	Index_TXT
BRL=X	Cotacao USD
EURBRL=X	Cotacao Euro

[brunolelli87]

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, 

How can I solve it?

Thanks

[zhadrakas]

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]

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

Look at that:

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]

Nop!

Look at that:

[jonathandienst · Accepted solution]

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

[brunolelli87]

Great!

It's working now,

Thanks