Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
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;
by using the purge char you get the value
Try this
Num(purgechar(PREMIUM, '+'),'##00.00') as PREM
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