Formatting with Fields - wrong values displayed

[oehmemat]

Hello Community,

I have some market data from a computer game. There are 3 markets each with its own currency and I would like to format the Values with the currency depending on the market the data came from.

My Problem is, when there are identical values in the source table, there are wrong values in the resulting table. It always seems to take the currency for the first found value and uses it for all other entries.

I was able to reproduce it with this:

Market:


load *,
	num(num#(AskPrice_tmp,'#,##0.00','.',','),'#.##0,00 '&Currency, ',' , '.' )as AskPrice;
load * inline
[Product,Currency,AskPrice_tmp
ZZZ,AAA,130.00
ZZZ,BBB,130.00
ZZZ,CCC,200.00];

The resulting table:

Product |Currency |AskPrice     |AskPrice_tmp 
ZZZ	|AAA	  |130,00 AAA	|130.00
ZZZ	|BBB	  |130,00 AAA	|130.00    <- "Ask Price" should be 130,00 BBB
ZZZ	|CCC	  |200,00 CCC	|200.00

I tried a couple of things, but wasn't able to get rid of this behaviour, any help is highly appreciated.

[Arthur_Fong · Accepted solution]

This happens  because you are forcing QV to read a string as numeric value.

So, QV will read the numeric value of 130 for AAA and display this value for the second row 130 BBB.

As both are 130 and is identical in terms of numeric value, QV will display this with the same text.

Try this:

load *,
//num(num#(AskPrice_tmp,'#,##0.00','.',','),'#.##0,00 '&Currency, ',' , '.' )as AskPrice;
AskPrice_tmp&' '&Currency as AskPrice;
load * inline
[Product,Currency,AskPrice_tmp
ZZZ,AAA,130.00
ZZZ,BBB,130.00
ZZZ,CCC,200.00
];

[oehmemat]

Thanks for the reply.

I always thought changing the Number formatting doesn't change the field type.  Qlikview recognizes the values from as a string as the thousands and decimal separators doesnt fit.

I decided to keep the numbers as they are and do the formatting in the UI