Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Inserted value not seen with database lookup in same transformation

  1. #1
    Join Date
    Jan 2017
    Posts
    8

    Default Inserted value not seen with database lookup in same transformation

    Hi guys,
    I have three steps occuring in the following order. The main prurpose is to generate an accumulated table measure:
    1. lookup the accumulated table for up-to-now summed value
    2. a calculator step which does the following sum : (previous looked up value from accumulated table + new value for read from previous steps)
    3. update/insert step to write to the accumulated table the new summed value.

    However after the transformation finished the accumulated value is the last value read. for example 1100, 2000, 3000 values must be summed and written to the accumulated table but the result is 1100. When I look at the logs I see "No result found after database lookup! (add defaults)" for all the values looked up. So the default 0 value is returned. Why am i getting the defaults do you think? I have cache disabled for lookup step and comit interval 1 for insert/update step.


    Thanks in advance.

  2. #2
    Join Date
    May 2014
    Posts
    358

    Default

    Because you are trying to write a row to a DB and expect that it will be there for the next row in the stream. That's not how Kettle works. At the moment you commit an inserted row, you may still have thousands of rows which are already somewhere past the lookup step but before the table insert.
    If you can describe more clearly how does your data look like and what output do you want to achieve, we can give you some tips on how to do it.

  3. #3
    Join Date
    Jan 2017
    Posts
    8

    Default

    Thanks Lukfi for the quick response.

    Actually what I was trying to do is something like this. Lets say I have columns
    A B 100
    A B 200
    C D 1000
    coming from a table input. I generated a fact table containing all these single lines as grain. The next step is to generate another fact table containing grouped values by the symbols having sums. So the second fact table should have
    A B 300
    C D 1000
    I thought that since I have just generated the bigger fact table, If I used the same transaction and rows I could calculate and create the aggregated table as well. Since all the dimensions are already lookedup, I thought it would be more faster than forming another transformation and running similar table input query plus databse looking ups for the dimension values once more. But I understand the logic behind I think. So I think I will create another transformation having a grouped by'ed table input as a first step. Or is there any chance of generating the two facts on the fly within a transformation what do you think?

    Here is what I have come up with up:

    http://imgur.com/a/4OvQw


    Quote Originally Posted by Lukfi View Post
    Because you are trying to write a row to a DB and expect that it will be there for the next row in the stream. That's not how Kettle works. At the moment you commit an inserted row, you may still have thousands of rows which are already somewhere past the lookup step but before the table insert.
    If you can describe more clearly how does your data look like and what output do you want to achieve, we can give you some tips on how to do it.
    Attached Images Attached Images  

  4. #4
    Join Date
    Jan 2017
    Posts
    8

    Default

    OK, I think I found a solution. Group by step It seems it works! Thank you

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.