topic Re: Re: Convert String to Number in QlikView

Convert String to Number

amber2000 — Fri, 14 Feb 2014 11:09:42 GMT

Hello everyone,

Hope you can help me with this tiny problem?

I want to convert a string to a number in the load script.

I've tried this but it doesn't work: Num#([COLLI]) as [SC Colli_Adjusted],

In my data I have lots of numbers (positif as well as negative) and they need to be added up:

-40

The result of the add should be 0, only in my report it shows 80 because the numbers -40 en 40 appear more than once and it seems that Qlikview loses them.

I can't change the datatype in the original file, it's from an outside source !!!!

Can anyone help me please?

Monique

Re: Convert String to Number

alexandros17 — Fri, 14 Feb 2014 11:14:15 GMT

It seems all ok (even if I should have used Num(Colli) as ...)

I think the problem is related to dimensions you have used in your chart

Let me know ...

Re: Convert String to Number

er_mohit — Fri, 14 Feb 2014 11:21:24 GMT

See the attached file

Re: Convert String to Number

ashwanin — Fri, 14 Feb 2014 11:22:54 GMT

Sum is zero after converting to num#(Field) to Field.

Put your formula in an input box, and check what value is reflecting there.

Re: Convert String to Number

hic — Fri, 14 Feb 2014 11:23:19 GMT

You are in principle doing the right thing. Num#() is the correct function. So I would guess that there is some other character that messes up the data, e.g. additional blanks or long hyphen instead of a minus sign.

Try

Num#(Trim(COLLI))

You can also see what you get if you analyse what the minus sign really is

Ord(Left(COLLI,1))

HIC

Re: Re: Convert String to Number

manojkulkarni — Fri, 14 Feb 2014 11:34:46 GMT

Pls check attached.. I have stored few values as text in excel & loaded to qlikview.

Re: Convert String to Number

amber2000 — Fri, 14 Feb 2014 11:37:37 GMT

Thank you Alessandro, the problem was situated in the chart where I created an Expression for that field, I had used this code:

= sum({$<[SC Article] = {"> 99999"}>}DISTINCT([SC Colli_Adjusted])) instead of

sum({$<[SC Article] = {"> 99999"}>}Num([SC Colli_Adjusted]))

It's working fine now 😉

Re: Convert String to Number

amber2000 — Fri, 14 Feb 2014 11:40:24 GMT

Thanks to all for your quick responses.