Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Keeping nulls while changing data type

  1. #1
    Join Date
    Jun 2007
    Posts
    112

    Default Keeping nulls while changing data type

    Hi Pentaho community!

    I'm working in kettle 2.5.

    I have a spreadsheet from a third party with 217 fields. Most of the data is numeric, but sometimes they throw in an alpha code.

    I am reading in the fields as strings, and using a Modified Java Script Value step to convert the alpha codes to numbers. Next, I use a Select values step to convert the data type to number.

    Instead of nulls in the spreadsheet, they have either two or four spaces. I trim the fields, so I guess the null fields are turned into zero-length strings. Unfortunately, the zero-length strings are being turned to 0.0 when I convert the data type form string to number.

    I can use a Null if... step before the Select Values step. This changes the zero-length strings to null, and they come through the type conversion as null. However, I have to input the names of all 217 fields by hand.

    Is there a better way?

    Thanks,
    Nathan

  2. #2
    Join Date
    May 2006
    Posts
    4,882

    Default

    Show me an example... my example is attached (but I don't see your problem). Change my example to show what you see.

    Regards,
    Sven
    Attached Files Attached Files

  3. #3
    Join Date
    Jun 2007
    Posts
    112

    Default

    Hi Sven,

    Here's my example. When I preview the results of Replace K I have what looks like null in some of the blanks. If I try to select contents of the cells in the preview results, I can get the cursor to go in the cell but there is nothing to select.

    However, when I preview the results of Change Measure Types to Numbers, I have 0.0 where I used to have blanks.

    Thanks for you help.

    Nathan
    Attached Files Attached Files

  4. #4
    Join Date
    Jun 2007
    Posts
    112

    Default

    Hi Sven,

    I've modified your example to show my problem. I'm using Spoon 2.5.0, and I get output of 0.0.

    Is there a JavaScript trick I could use to set the values to null?

    I have tried something along the lines of

    for( var i = 6; i < row.size(); i++ )
    {
    var value = row.getValue(i);
    if( value == "")
    {

    But I get stuck there. I can't do value.setValue(null) because then JavaScript wouldn't know what type to use.

    Thanks again for looking at this.

    Nathan
    Attached Files Attached Files

  5. #5
    Join Date
    Jun 2007
    Posts
    112

    Default

    I tested my version of the example transform in spoon 3.0 and it throws an error at the select values step. It says "" is an unparsable number.

  6. #6
    Join Date
    May 2006
    Posts
    4,882

    Default

    So you mean like field a4 in the example attached to this post?

    Regards,
    Sven
    Attached Files Attached Files

  7. #7
    Join Date
    Jun 2007
    Posts
    112

    Default

    Yes, the spreadsheet fields are like field a4 in the last example. Unfortunately, the "blank" cells that actually contain spaces don't contain a consistent number of spaces. Sometimes they are " ", and sometimes they are " ".

    I thought an efficient way to take care of this would be to trim the spreadsheet fields upon input. That changes the spaces fields from " " to "". I was hoping "" would convert to null when I changed the data type to number, but I guess it doesn't.

    I think my solution will be to use a null if step to change "" to null before I convert the data type.

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.