Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Num# not working to convert text item

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!

1 Solution

Accepted Solutions

Re: Num# not working to convert text item

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;

4 Replies

Re: Num# not working to convert text item

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;

vsudhakar
Contributor III

Re: Num# not working to convert text item

by using the purge char you get the value

Re: Num# not working to convert text item

Try this

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

MVP
MVP

Re: Num# not working to convert text item

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Community Browser