topic Re: Turn text numbers to number numbers in QlikView

Turn text numbers to number numbers

Thu, 06 Sep 2012 14:35:56 GMT

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?

Re: Turn text numbers to number numbers

Thu, 06 Sep 2012 14:53:38 GMT

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

Re: Turn text numbers to number numbers

Thu, 06 Sep 2012 14:54:34 GMT

Can you copy and paste the table as an example?

Re: Turn text numbers to number numbers

Thu, 06 Sep 2012 14:59:30 GMT

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

Re: Turn text numbers to number numbers

Thu, 06 Sep 2012 15:02:47 GMT

try this:

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

Replaceing ( with - is still creating a string

Re: Turn text numbers to number numbers

Thu, 06 Sep 2012 15:15:49 GMT

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

Re: Turn text numbers to number numbers

Thu, 06 Sep 2012 15:18:22 GMT

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

Re: Turn text numbers to number numbers

Thu, 06 Sep 2012 15:36:52 GMT

Heres my document

Re: Turn text numbers to number numbers

Thu, 06 Sep 2012 15:51:39 GMT

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.

Re: Turn text numbers to number numbers

Thu, 06 Sep 2012 16:08:05 GMT

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!