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

[SS_I]

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.

[lblumenfeld]

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.

[SS_I]

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. 

[rwunderlich]

What datasource are you loading from?

-Rob

[SS_I]

From SQLServer to QVD then onto Dashboard.

[lblumenfeld]

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.

[Brett_Bleess]

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

[rwunderlich]

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

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

-Rob