Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: After addt. Steps - like lookup - data from database are modified

  1. #1

    Default After addt. Steps - like lookup - data from database are modified

    Hi folks,
    I'm confused ... I read data from a table.
    I joined the table with a database join step or alternatively with a lookup.
    In both cases, there are configurations, where it works, but in most cases, even after playing and testing around, I behaved strange:
    Data for the amtsourcecr and -dr were manipulated.
    The preview from the table works fine with negative and positive figures, but sometimes after the 1st or after the 2nd the column is positive only or zeroed.
    This behaviour seems to be different depending on the sequence of the steps.
    What is wrong with this transformation or my approach?
    I enclose the ktr - but sure, you don't have the data/tables, but probably it's easier to understand my approach ..!

    I tested in 3.2 and 4, same behaviour. So probably my fault?

    Thanks, Alexander
    Attached Files Attached Files

  2. #2

    Default screenshot from the GUI

    from the screenshot you can see the issue, I'm facing:
    In the 1st shot the data are ok, in the second, taken from a later on action/step it's wrong.
    Name:  SS-2011-01-18_22.01.38.jpg
Views: 37
Size:  30.0 KB
    Name:  SS-2011-01-18_22.02.14.jpg
Views: 40
Size:  30.4 KB

  3. #3
    Join Date
    Sep 2009


    Hi obernau,

    regarding the problem I've no idea where it is My assumption is that you're missing an ORDER BY clause, thus the rows come in unpredictable order from the DB and that might be confusing when looking at the data from different runs.

    One piece of advice: maybe you'd like to sanitize your sample first. You left a hundred or so connections including username and password in your demo transformation...



  4. #4


    Hi Slawo,
    Thanks for your thoughts, sure I did a test run with an order by clause, but same result.

    To all:
    What is really concerning: When doing this calculation for the amount fields within the SQL select statement it behaves exactly in the same way, as doing it later on, separately in a Javascript step.
    And only the (for me most important fields/columns in this transformation ...) most important fields are affected and manipulated.
    Why these fields and not the others too?!
    And the content from one column is copied over the content of an other column!!! Seems the data in the java virtual machine are screwed up.
    What could be the root cause?
    Cheers, Alexander

  5. #5
    Join Date
    Sep 2007


    I don't see anything wrong with your transformation.
    But I'm not sure I understand you problem:
    what do you mean by:
    - data is affected and manipulated?
    - the content from one column is copied over the content of an other column?

    Just another question: Are you simply doing previews on data? Or did you run the transformation? A Preview is just that, Kettle shows you a sample of the data on that step. That doesn't mean that every time you do a preview you will see the same.
    I suggest you to run the transformation and see if the final text file looks the same or not after each time you do it.

  6. #6

    Default 3 columns are screwed up: all are related to amount ...

    Hi Maria,
    THere are two columns with debted and credited and the 3rd is the result of their difference, calculated within the select statement.
    In all rows only one of debted or credited is filled with data, positive figure or negative.
    Depending on the difference, the result could be 0 minus something, so a negative result or something minus 0 -> positive figure.
    If you take a look to the two screenshots I attached, you get the understanding, what is going wrong.
    And sure, I didn't run a preview only! Same result in the file written from the transformation.
    As I wrote earlier, there are different results from the preview, directly after the table/select step, the stream and the columns are ok.
    But after a join step they are affected as I described earlier.
    Thanks, Alexander

  7. #7
    Join Date
    Apr 2008


    Can you add an Order by clause to your table input, and then do a sort rows after the JS step?
    This will help make sure that the rows are in the exact same order, making a comparison possible.
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  8. #8
    Join Date
    Sep 2007


    Note that you are declaring the var 'total' twice. Could this be a possible cause of error? don't know.

    Your code is simple; if I were you, I would replace the JS step with a UDJE.

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.