Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
tamilarasu
Champion
Champion

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;

View solution in original post

4 Replies
tamilarasu
Champion
Champion

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;

Anonymous
Not applicable
Author

by using the purge char you get the value

Kushal_Chawda

Try this

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

jonathandienst
Partner - Champion III
Partner - Champion III

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