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

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

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

14 Replies
nstefaniuk
Creator III
Creator III

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

Not applicable
Author

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

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

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

-Rob

Not applicable
Author

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)

50

40

200

-

10

(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.

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

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

-Rob