Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
oehmemat
Contributor III
Contributor III

Formatting with Fields - wrong values displayed

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.

1 Solution

Accepted Solutions
Arthur_Fong
Partner
Partner

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

View solution in original post

2 Replies
Arthur_Fong
Partner
Partner

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

View solution in original post

oehmemat
Contributor III
Contributor III
Author

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