Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
amber2000
Creator
Creator

Convert String to Number


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

1 Solution

Accepted Solutions
alexandros17
Partner - Champion III
Partner - Champion III

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

View solution in original post

7 Replies
alexandros17
Partner - Champion III
Partner - Champion III

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
Master II
Master II

See the attached file

ashwanin
Specialist
Specialist

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
Former Employee
Former Employee

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
Partner - Specialist II
Partner - Specialist II

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

amber2000
Creator
Creator
Author

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
Creator
Creator
Author

Thanks to all for your quick responses.