Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi community,
I have a field of numbers. For some reason some of them are text and some of them are numbers. Is there a way to get the text numbers to have a number format?
Ok you had some leading/trailing spaces on the field. Wrap your field name with trim() to remove these and it works fine. I tested it using the field e
if(left(trim(e),1)='(', (num(replace(replace(trim(e), '(',''), ')', ''))) * (-1),num(replace(replace(trim(e), '(',''), ')', '')))
Edit: Sometimes the easiest way to figure this out if you are not sure is to create an expression in a list box with the fomula: ='@' & e & '@'
This creates a string and will show you hidden spaces.
@37,778.00@
@19,285.00@
@17,460.00@
@34,234.00@
@33,087.00@
@19,557.00@
@(3,412) @
@(83,106) @
@16,709.00@
@8,893.00@
@6,346.00@
@16,884.00@
@10,188.00@
@2,056.00@
@21,688.00@
@9,002.00@
@7,458.00@
@20,650.00@
@(17,338) @
Also note that qlikview sees spaces as characters. you may not know they are there, but fields will not match up if they are the same, but one has no trailing space and one has trailing space.
num# isn't working for some reason as they still show up in the list box with text alignment.
Can you copy and paste the table as an example?
I go from:
(4302)
5069
7869
to:
-4302
5069
7869
The code I used in the script to convert the field:
Num#(replace(replace(NumTextField, '(', '-'), ')', ''))
try this:
if(left(NumTextField,1)='(', num#(replace(replace(NumTextField, '(',''), ')', ''))*(-1),num#(replace(replace(NumTextField, '(',''), ')', '')))
Replaceing ( with - is still creating a string
Thats still not working, with that code it just drops the negative values.
No, that code instead of adding - to the value which creates a string, it removes the parenthesis and multiply's by -1 to give it an actual negative value.
Can you post a sample?
Edit:
I just tested it by inputting a string into the expression and it does exactly what is needed:
if(left('(4302)',1)='(', num#(replace(replace('(4302)', '(',''), ')', ''))*(-1),num#(replace(replace('(4302)', '(',''), ')', '')))
this returns the number -4302
Heres my document
Ok you had some leading/trailing spaces on the field. Wrap your field name with trim() to remove these and it works fine. I tested it using the field e
if(left(trim(e),1)='(', (num(replace(replace(trim(e), '(',''), ')', ''))) * (-1),num(replace(replace(trim(e), '(',''), ')', '')))
Edit: Sometimes the easiest way to figure this out if you are not sure is to create an expression in a list box with the fomula: ='@' & e & '@'
This creates a string and will show you hidden spaces.
@37,778.00@
@19,285.00@
@17,460.00@
@34,234.00@
@33,087.00@
@19,557.00@
@(3,412) @
@(83,106) @
@16,709.00@
@8,893.00@
@6,346.00@
@16,884.00@
@10,188.00@
@2,056.00@
@21,688.00@
@9,002.00@
@7,458.00@
@20,650.00@
@(17,338) @
Also note that qlikview sees spaces as characters. you may not know they are there, but fields will not match up if they are the same, but one has no trailing space and one has trailing space.
I think trimming helped but you used e which I already had parsed some in the script. I think you need to use num# before you replace and also num after you replace the '()'. Thanks though, I got it to work from your comment!