topic Re: Value Including % symbol is being read as numeric value in Qlik in QlikView

Value Including % symbol is being read as numeric value in Qlik

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

Qlik randomly converting strings from SQL into numeric, and when it detects a " %" in the string, it converts it to a decimal (fraction) instead of leaving the text intact.

For Example: 9.5 %(symbol with space after value) when read into Qlik it is being converted into 0.095. Which shouldn't be the case. Any thoughts of why this could be happening or did we have any similar cases before? Any input is appreciated. Thanks.

Re: Value Including % symbol is being read as numeric value in Qlik

lblumenfeld — Thu, 23 Jan 2020 05:08:57 GMT

Qlik looks at the data in the field and tries to determine its type. It then stores it according to what it determines.

9.5 % when converted to a number is 0.095.

Do you want this to stay as the text field "9.5 %"? If so then do this...

Let's assume that X is the field name.

Load
Text(X) as X,
.
.
etc....

I hope that is helpful.

Re: Value Including % symbol is being read as numeric value in Qlik

SS_I — Thu, 23 Jan 2020 16:34:53 GMT

Thanks for the input. It helps. But Is there a reason of why this could be happening in first place? Even before forcing the field to be text, this conversion into decimals when detecting % symbol happened very randomly. For approximation - If I had about 10000 rows this randomly happened to one or two rows

Field Output

3.4 % 3.4
6.2 % 6.2
9.8 % 0.098

Forcing Field value to be string will work....but I am trying to understand the reason, when it can read other rows to be just as value what is potentially wrong with the third row to consider the field value as numeric.

Re: Value Including % symbol is being read as numeric value in Qlik

rwunderlich — Fri, 24 Jan 2020 03:36:19 GMT

What datasource are you loading from?

-Rob

Re: Value Including % symbol is being read as numeric value in Qlik

SS_I — Fri, 24 Jan 2020 15:20:18 GMT

From SQLServer to QVD then onto Dashboard.

Re: Value Including % symbol is being read as numeric value in Qlik

lblumenfeld — Fri, 24 Jan 2020 17:01:24 GMT

It looks like your data is

10 % .. with a space between the number and the percent sign.

It looks like Qlik understands that as a number, but only when there's a decimal after the number. So, 10.00 % works, but 10 % doesn't.

I loaded some random values and Qlik is actually storing the data as xx %, so it sees it as character. When I render the field in a visualization it converts it to a number, but only if there is a decimal place.

I loaded

5.35 %
3 %
25.00 %

5.35 % renders properly. So does 25 %. But, 3 % does not.

Re: Value Including % symbol is being read as numeric value in Qlik

Brett_Bleess — Wed, 19 Feb 2020 19:07:39 GMT

Have a look at the following Design Blog post, believe it may help explain the why part of things for you!

https://community.qlik.com/t5/Qlik-Design-Blog/Automatic-Number-Interpretation/ba-p/1475352

Here is the base Design Blog URL in case you want to search further in this area, there are over 700 posts from our experts on different topics and most are how-to related or explanations of topics.

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett

Re: Value Including % symbol is being read as numeric value in Qlik

rwunderlich — Wed, 19 Feb 2020 23:29:22 GMT

Random. Hmm.... Is Num() being used in the script?

https://qlikviewcookbook.com/2020/02/num-script-vs-chart/

-Rob