Hitachi Vantara Pentaho Community Forums
Results 1 to 9 of 9

Thread: Couldn't convert String to Number

  1. #1
    Join Date
    Aug 2010
    Posts
    11

    Default [Solved]Couldn't convert String to Number

    Hi,


    I have a problem during a transformation when I want to parse a String to a decimal number

    The transormation consists of
    - Reading values from an excel file. (the values are students names and grades, the grades are numbers like 9.5 but if a student is not present during an exam he gets a "DEF" value meaning he failed the course. I want to replace The DEF value by a 0 value)
    - When I read the fields from the excel file I read them all like string (due to the DEF value) then with a replace in string step I replace the DEF values with 0 values.
    - The problem is when I want to convert the string values into number values I get the following errors

    Couldn't convert String to Number, Unparseable Number "0.5"

    It seems it has to do with the decimal separator (. or ,)

    I'm quite lost here and would really aprreciate a little help.

    Thanks.

    PS I joined the .ktr files and the excel file.
    Attached Files Attached Files
    Last edited by exhortae; 08-17-2010 at 06:39 PM.

  2. #2
    Join Date
    Aug 2010
    Posts
    2

    Default parseFloat

    I'm pretty new to Pentaho myself and I struggled with this for a little bit too. I eventually ended up using the 'Modified Java Script Value' step from the Scripting folder. I created a new variable and used parseFloat() to parse out the decimals.


    It should look something like this:

    var [your new variable name] = parseFloat([field of string you want to parse]);

    I hope this helps.

  3. #3
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    You can specify the Decimal (. or ,) symbol in most steps that allow data conversion, including "Excel Input" and "Select Values".

  4. #4
    Join Date
    Aug 2010
    Posts
    11

    Default

    Quote Originally Posted by MattCasters View Post
    You can specify the Decimal (. or ,) symbol in most steps that allow data conversion, including "Excel Input" and "Select Values".
    I don't know what I'm doing wrong but that's what I've been trying to do with no success.


  5. #5
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    How are you converting? What's the mask, etc?

  6. #6
    Join Date
    Aug 2010
    Posts
    11

    Default

    Quote Originally Posted by datageek View Post
    I'm pretty new to Pentaho myself and I struggled with this for a little bit too. I eventually ended up using the 'Modified Java Script Value' step from the Scripting folder. I created a new variable and used parseFloat() to parse out the decimals.


    It should look something like this:

    var [your new variable name] = parseFloat([field of string you want to parse]);

    I hope this helps.

    It does the trick but the problem is that my field names are like Optimi(1) and when I pass it as a parameter to parseFloat (parseFloat(Optimi(1)) as you can guess I get an error saying Optimi(1) is not a function), so I have to rename all the fileds in order to remove the parenthesis (and I have like 120 differents fields with names with parenthesis) so this solution would be too much trouble to use.

    But at least it's working.

  7. #7
    Join Date
    Aug 2010
    Posts
    11

    Default

    Quote Originally Posted by MattCasters View Post
    How are you converting? What's the mask, etc?
    I've juste tried this for example and it's not working


  8. #8
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Ah I see, there is a space in front of the mask. I guess the space comes from the Excel sheet somehow.
    Put a space in front of the Format mask: " #.#;-#.#" (without "")

  9. #9
    Join Date
    Aug 2010
    Posts
    11

    Default

    Quote Originally Posted by MattCasters View Post
    Ah I see, there is a space in front of the mask. I guess the space comes from the Excel sheet somehow.
    Put a space in front of the Format mask: " #.#;-#.#" (without "")
    Yes, when the data comes out the excel step the numbers have a leading space in them (which is akward because in the excel sheet there is NO space).

    " #.#;#.#" works now.


    Thank you, I spent hours on it with no success.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.