topic Re: Num# not working to convert text item in QlikView

Num# not working to convert text item

Tue, 10 May 2016 10:31:04 GMT

Hello all

I have a report where I have premium values that are like the following.

+0000000000000000000005.0136

and

-0000000000000000000005.0136

I have tried purging the '+' from the text item then converting them to a number such as below.

Num#(purgechar(PREMIUM, '+'),'###,###;###,###-',',','.') as PREM

Ultimately I want the data to show a positive 5.0136 and a negative 5.0136

When using the code above the "numbers" still act like a text item and when extracting the to Excel they still act like text items but can be converted to numbers and Excel also removes the leading zeros.

Even when the '+' has been removed and Num function used the field still behaves like a text item.

However, in Qlikview there seems to be no leeway in turning this text item into a number.

I have tried to do this within the "number tab" under table box properties but the premium value still behaves in the same way.

Any ideas?

Thanks any help is appreciated!

Re: Num# not working to convert text item

tamilarasu — Wed, 11 May 2016 02:04:48 GMT

Hi Ryan,

You can try any one of the below methods,

Num(Evaluate(PurgeChar(PREMIUM,'+'))) as PREM;

Replace(LTrim(Replace(PurgeChar(PREMIUM, '+'), '0', ' ')), ' ', 0) as PREM;

Re: Num# not working to convert text item

Anonymous — Wed, 11 May 2016 04:45:01 GMT

by using the purge char you get the value

Re: Num# not working to convert text item

Kushal_Chawda — Wed, 11 May 2016 04:55:28 GMT

Try this

Num(purgechar(PREMIUM, '+'),'##00.00') as PREM

Re: Num# not working to convert text item

jonathandienst — Wed, 11 May 2016 04:56:07 GMT

No need to purge or other convolutions. Just use Num (not Num#). Both of these work fine:

Num(-0000000000000000000005.0136) --> -5.0136

Num(+0000000000000000000005.0136) --> 5.0136