topic Re: Show a different value on a ListBox in QlikView

Show a different value on a ListBox

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

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?

Re: Show a different value on a ListBox

jonathandienst — Tue, 26 May 2020 07:21:16 GMT

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.

Re: Show a different value on a ListBox

zhadrakas — Tue, 26 May 2020 07:26:44 GMT

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

Re: Show a different value on a ListBox

brunolelli87 — Tue, 26 May 2020 07:52:15 GMT

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

Re: Show a different value on a ListBox

zhadrakas — Tue, 26 May 2020 08:13:33 GMT

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')

Re: Show a different value on a ListBox

brunolelli87 — Tue, 26 May 2020 14:12:40 GMT

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!

Re: Show a different value on a ListBox

brunolelli87 — Tue, 26 May 2020 14:27:51 GMT

Nop!

Look at that:

Re: Show a different value on a ListBox

jonathandienst — Tue, 26 May 2020 14:33:47 GMT

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

Re: Show a different value on a ListBox

brunolelli87 — Tue, 26 May 2020 16:14:07 GMT

Great!

It's working now,

Thanks