topic Re: Text to Number Formatting Issue in QlikView

Text to Number Formatting Issue

qliksus — Thu, 21 May 2015 21:11:47 GMT

Hi,

I have loaded data from excel joined with inline data, but excel data considered as Number where as Inline data considered inside Qlikview as Text which doesn't create proper link.

Pls find the attached JPEG file where you can see Excel data is in number and Inline is in Text where as both case numbers are same in both cases.

Can anyone help me to solve this?

Re: Text to Number Formatting Issue

Clever_Anjos — Thu, 21 May 2015 21:15:43 GMT

Probably you are outpassing maximum number in QlikView.

Please treat all records as text using text#()

Re: Text to Number Formatting Issue

Anonymous — Thu, 21 May 2015 21:17:39 GMT

Try this:

num(Num,'#,##0')

Re: Text to Number Formatting Issue

qliksus — Thu, 21 May 2015 21:32:45 GMT

Hi Michael,

Thanks for the reply, but above thing won't work.

Regards,

Susant

Re: Text to Number Formatting Issue

Anonymous — Thu, 21 May 2015 21:49:27 GMT

Apparently as cleveranjos‌ says, you've exceeded Qlik's precision limit.

Re: Text to Number Formatting Issue

MarcoWedel — Thu, 21 May 2015 22:21:19 GMT

Hi,

one possible solution could be:

LOAD *,
    If(IsNum(Inline),Inline,Right(Inline,14)+Left(Inline,Len(Inline)-14)*1E14) as Inline2,
    If(IsNum(Inline),Inline,Num(Right(Inline,14)+Left(Inline,Len(Inline)-14)*1E14,'(dec)')) as Inline3
Inline [
Inline
9
98
982
9826
98261
982617
9826170
98261706
982617063
9826170633
98261706330
982617063302
9826170633029
98261706330294
99999999999999
100000000000000
982617063302940
982617063302941
982617063302942
982617063302943
982617063302944
982617063302945
982617063302946
982617063302947
982617063302948
982617063302949
982617063302950
982617063402934
982617063402940
];

hope this helps

regards

Marco

Re: Text to Number Formatting Issue

PrashantSangle — Fri, 22 May 2015 04:40:25 GMT

Hi,

Take look at evaluate()

Read below blog

https://community.qlik.com/blogs/qlikviewdesignblog/2015/01/30/evaluate-function

It might help you.

Regards

Re: Text to Number Formatting Issue

qliksus — Fri, 22 May 2015 09:45:25 GMT

Hi Max dreamer,

Thanks for the reply. It worked

Re: Text to Number Formatting Issue

qliksus — Fri, 22 May 2015 09:46:30 GMT

Hi Marco,

Thanks for the reply. Can i get the script written for Inline-3 as some portionn is cutting -off in the above comments.

Re: Text to Number Formatting Issue

MarcoWedel — Fri, 22 May 2015 10:59:13 GMT

Hi,

If(IsNum(Inline),Inline,Num(Right(Inline,14)+Left(Inline,Len(Inline)-14)*1E14,'(dec)')) as Inline3

hope this helps, although I think Max found the easier solution.

regards

Marco

Re: Text to Number Formatting Issue

qliksus — Fri, 22 May 2015 14:10:32 GMT

Thanks Marco. Just want tom know what is the output of using '(dec)'

Re: Text to Number Formatting Issue

Clever_Anjos — Fri, 22 May 2015 15:51:59 GMT

Be careful about this

"Note that while using Evaluate() returns a number, it is only stored with 14 digit precision. While you may be able to see more than 14 digits, this is just the formatting. The internal mantissa is only 14 digits."