Convert String to Number

[amber2000]

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

-40

-40

-40

80

40

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

[alexandros17 · Accepted solution]

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

[er_mohit]

See the attached file

[ashwanin]

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

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

[hic]

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

[manojkulkarni]

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

[amber2000]

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 😉

[amber2000]

Thanks to all for your quick responses.