topic Re: Issue with formating in QlikView

Issue with formating

varunreddy — Wed, 19 Oct 2016 20:02:06 GMT

Hi All,

I am struggling in performing sum function as QlikView is not understanding the format.

I have numbers in the below format:

00000000123456

00000003456789.

When I try to add both the numbers I get 0.

Can any one please explain me how to add these two numbers?

Thanks in advance!

Cheers,

Varun Reddy

Re: Issue with formating

sunny_talwar — Wed, 19 Oct 2016 20:03:55 GMT

Do you want to keep the 0s in the front after the addition also? May be like this if you don't want the 0s

Num#(Number1) + Num#(Number2)

Re: Issue with formating

varunreddy — Wed, 19 Oct 2016 20:08:26 GMT

Sunny,

Those two values are from same field.

I tried this:

num#([Total Associated Financial Measure Value]) as [Total Associated Financial Measure Value]

In front end, I tried:

Sum(Total Associated Financial Measure Value] ).

It didn't work. I get 0.

Re: Issue with formating

sunny_talwar — Wed, 19 Oct 2016 20:10:12 GMT

How about when you try this:

[Total Associated Financial Measure Value] * 1 as [Total Associated Financial Measure Value]

Re: Issue with formating

varunreddy — Wed, 19 Oct 2016 20:12:00 GMT

I am getting this:

Re: Issue with formating

sunny_talwar — Wed, 19 Oct 2016 20:16:26 GMT

I think you have 14 digit number and that is what is causing issue. May be you want to try removing a 0 and see if that helps

Num#(Right([Total Associated Financial Measure Value], 13)) as [Total Associated Financial Measure Value]

Re: Issue with formating

varunreddy — Wed, 19 Oct 2016 20:25:44 GMT

Sunny,

Can you explain what it is doing?

If I have 000000000000074.75, it is rounding it to 75. What exactly it is doing and we are hardcoding the number to 13. Can you please explain?

Re: Issue with formating

sunny_talwar — Wed, 19 Oct 2016 20:28:40 GMT

Because QlikView have issue in reading numbers which are more than 14 digits, I am just keeping the last 13 digits (ignoring the 1st zero). For the rounding, have you tried

Num(Num#(Right([Total Associated Financial Measure Value], 13)), '#,##0.00') as [Total Associated Financial Measure Value]

Re: Issue with formating

varunreddy — Wed, 19 Oct 2016 20:31:06 GMT

Yes. It is still rounding.

Re: Issue with formating

varunreddy — Mon, 24 Oct 2016 18:36:44 GMT

Hi Sunny,

Thanks for your response. Is there any alternative if there are more than 14 digits?

Cheers,

Varun Reddy.

Re: Issue with formating

sunny_talwar — Mon, 24 Oct 2016 18:38:53 GMT

Check out this link:

https://gist.github.com/ralfbecher/6550467

Re: Issue with formating

varunreddy — Mon, 24 Oct 2016 18:43:14 GMT

I cannot open this.

I get this error message:

Re: Issue with formating

sunny_talwar — Mon, 24 Oct 2016 18:45:39 GMT

Can you try one more time?

Loading numbers in QlikView with more than 14 digits leads into scientific (exponential) notation. The 64-bit IEEE float…

Re: Issue with formating

varunreddy — Mon, 24 Oct 2016 18:51:13 GMT

No luck

Re: Issue with formating

sunny_talwar — Mon, 24 Oct 2016 18:58:02 GMT

Ralf used a load statement like this:

LOAD Div(Field, 1e10) & left(repeat('0',10), 10 - len(text(Mod(Field, 1e10)))) & Mod(Field, 1e10) as MyNum

Re: Issue with formating

varunreddy — Mon, 24 Oct 2016 18:59:36 GMT

Will try and update Sunny. Thanks again

Cheers,

Varun Reddy. K