Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Replace negative values with 0?

  1. #1
    Join Date
    Aug 2014
    Posts
    5

    Default Replace negative values with 0?

    Hello, I use Pentaho to update a database with values from multiple flat files and some of them contain negative values i.e "-20". I'm looking for a way to always write "0" to the database whenever a negative value is present (my final destination for the data does not handle negatives).

    btw I'm using a number format, not text.


    Thanks in advance for any help with this isuse.

  2. #2
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    You can use Filter-Rows and Set-Field-Value-To-A-Constant or just a User-Defined-Java-Expression for this.
    So long, and thanks for all the fish.

  3. #3
    Join Date
    Aug 2008
    Posts
    563

    Default

    While it is easy to do in PDI what you ask for ... I'd be cautious: You are saying that your database does not handle negative values? You might be in fact throwing away valuable data ... you might want to consider changing the column type of the table to cater for negative values. I don't know your scenario, but in general, it doesn't quite sound right to me. Just my 5 cents
    Best regards,
    Diethard
    ===============
    Visit my Pentaho blog which offers some tutorials mainly on Kettle, Report Designer and Mondrian
    ===============

  4. #4
    Join Date
    Aug 2014
    Posts
    5

    Default

    Quote Originally Posted by diddy View Post
    While it is easy to do in PDI what you ask for ... I'd be cautious: You are saying that your database does not handle negative values? You might be in fact throwing away valuable data ... you might want to consider changing the column type of the table to cater for negative values. I don't know your scenario, but in general, it doesn't quite sound right to me. Just my 5 cents
    Understood and you're right of course, the actual problem is downstream from this particular table not in the database itself. In this particular case, the negatives are functionally the same as 0's.

    As far as it being easy to convert those negatives into 0's.. I'm actually struggling with that a little.

    I'm looking at two articles
    http://wiki.pentaho.com/display/EAI/Filter+Rows
    http://wiki.pentaho.com/display/EAI/...+to+a+constant


    And I think I get the basic idea (isolate the negatives with filter rows and then feed that value to set a constant) but I'm spinning my wheels on exactly how to go about it.

    But, if Pentaho has taught me anything it's that perseverance and applying what I've learned elsewhere pays off.

    Thanks

  5. #5
    Join Date
    Aug 2014
    Posts
    5

    Default

    ugh, I love to overcomplicate things, this did the trick as a final script when the rest was done writing

    UPDATE mydb.mytable
    SET qty = '0'
    WHERE qty < 0;

  6. #6
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Quote Originally Posted by bgreenway View Post
    the actual problem is downstream from this particular table not in the database itself. In this particular case, the negatives are functionally the same as 0's.
    In that case don't use Kettle to execute an UPDATE statement, just leave the negative values alone.
    CREATE a VIEW to address the requirements of any data consumer.
    So long, and thanks for all the fish.

Tags for this Thread

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.