topic Re: How to a string field with () designating negative into a num type? in QlikView

How to a string field with () designating negative into a num type?

Thu, 06 Sep 2012 13:34:10 GMT

Hi community,

Is there a simple way or does anyone have anyway to turn negative strings with () into -.

example of field data:

9056

11209

(495)

(44245)

49300

Re: How to a string field with () designating negative into a num type?

Nicole-Smith — Thu, 06 Sep 2012 13:43:54 GMT

You have to change the Format Pattern on the Number tab:

For example, change ###########,### to ###########,###;-###########,### for a -

or change ###########,### to ###########,###;(###########,###) for ()

Re: How to a string field with () designating negative into a num type?

nstefaniuk — Thu, 06 Sep 2012 14:45:10 GMT

Hi you can use:

Num#(replace(replace('(14)', '(', '-'), ')', ''))

Put your field instead of '(14)' in the formula.

Re: How to a string field with () designating negative into a num type?

Thu, 06 Sep 2012 14:57:46 GMT

That is exactly what I had done but it still gives me text data type. I go from:

(4302)

5069

7869

to:

-4302

5069

7869

Re: How to a string field with () designating negative into a num type?

nstefaniuk — Thu, 06 Sep 2012 15:15:45 GMT

When you say "text data" you mean the display in a chart is the display used for text ?

Re: How to a string field with () designating negative into a num type?

nstefaniuk — Thu, 06 Sep 2012 15:22:01 GMT

In my test it works.

Please find below

Re: How to a string field with () designating negative into a num type?

Thu, 06 Sep 2012 15:31:46 GMT

I'm not sure what type the data is, but in a list box it aligns the way text data is supposed to. Other numbers align to the right.

Re: How to a string field with () designating negative into a num type?

Thu, 06 Sep 2012 15:36:03 GMT

Here's my document

Re: How to a string field with () designating negative into a num type?

nstefaniuk — Thu, 06 Sep 2012 15:40:18 GMT

Ok got it.

Remove the "," too when you replace the "( )".

Re: How to a string field with () designating negative into a num type?

Thu, 06 Sep 2012 15:43:30 GMT

Sorry can you show me the correct code. I don't see an extra ,

Re: How to a string field with () designating negative into a num type?

nstefaniuk — Thu, 06 Sep 2012 15:52:04 GMT

I can't refresh your application, but I think that the issue comes from "," like in "-3,412".

"-3,412" is -3412 or -3.412 for you ?

try Num#(replace(replace(replace(value, '(', '-'), ')', ''), ' ',''))

Re: How to a string field with () designating negative into a num type?

Thu, 06 Sep 2012 16:09:13 GMT

Look to this thread for the eventually answer to this stream.

http://community.qlik.com/message/256279#256279

Re: How to a string field with () designating negative into a num type?

rwunderlich — Thu, 06 Sep 2012 20:14:16 GMT

The replace() thing looks too complex to me. I would do it like this:

num(num#(value, '#;(#)'), '#;-#')

-Rob

Re: How to a string field with () designating negative into a num type?

Tue, 19 Mar 2013 05:37:57 GMT

HI Rob, the solutions works fine for me....but my problem is that I am having character - also in my fields for ex.

100

(0.1)

200

(0.23)

In such case the values (0.23) gets converted to -0.23 but - values remains as it is..Ideally it should be converted to 0 to work with operators.

I am putting my query into the same thread as my query is an extension to the samequery

. Do suggest If i need to put up this into a new thread.

Thanks

Lax.

Re: How to a string field with () designating negative into a num type?

rwunderlich — Sat, 23 Mar 2013 17:37:45 GMT

You can replace the - values with either an if() or a map. For example,

if(myfield='-',0,myfield) as myfield

-Rob