Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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

Re: Turn text numbers to number numbers

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.

9 Replies
Not applicable

Re: Turn text numbers to number numbers

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

Not applicable

Re: Turn text numbers to number numbers

Can you copy and paste the table as an example?

Not applicable

Re: Turn text numbers to number numbers

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

Re: Turn text numbers to number numbers

try this:

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

Replaceing ( with - is still creating a string

Not applicable

Re: Turn text numbers to number numbers

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

Not applicable

Re: Turn text numbers to number numbers

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

Re: Turn text numbers to number numbers

Heres my document

Not applicable

Re: Turn text numbers to number numbers

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

Re: Turn text numbers to number numbers

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!

Community Browser