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

Turn text numbers to number numbers

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?

1 Solution

Accepted Solutions
Not applicable
Author

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.

View solution in original post

9 Replies
Not applicable
Author

num# isn't working for some reason as they still show up in the list box with text alignment.

Not applicable
Author

Can you copy and paste the table as an example?

Not applicable
Author

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, '(', '-'), ')', ''))

Not applicable
Author

try this:

if(left(NumTextField,1)='(', num#(replace(replace(NumTextField, '(',''), ')', ''))*(-1),num#(replace(replace(NumTextField, '(',''), ')', '')))

Replaceing ( with - is still creating a string

Not applicable
Author

Thats still not working,  with that code it just drops the negative values.

Not applicable
Author

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

Not applicable
Author

Heres my document

Not applicable
Author

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.

Not applicable
Author

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!