Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: updated a status table after datas loading

  1. #1
    Join Date
    Aug 2007
    Posts
    4

    Default updated a status table after datas loading

    Hello All,

    I use Pentaho Data Integration to load a MYSQL database from txt files.
    To allow a visibility on good or bad loading for the administrator i have created a table t1 mainly with fields status ( takes values OK or KO), date_loading (with last loading date) and domain (contains a string like "financial","development").
    At each loading and so by domain i would like to compare the field date_extract of a txt file f1 with the value of t1's date_loading field.
    Then if f1's date_extract field value > t1's date_loading field value then i would like to update the field status with "OK" value and t1's date_loading field value with f1's date_extract field value else i would like to update the field status with "KO" .

    I have tried many way to do it without success.

    Please help me.

    boulanou

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

    Default

    Well... if you want to compare 2 dates they have to be in the same row.

    What you could do is:
    1) Make a stream that reads the date you want to compare from your table
    2) Extract the date to compare with from the file (you only want to have 1 date).

    joining both streams using (cartesian) join and then you will have 1 row containing both dates. then you can filter and let e.g. the transformation abort if the situation is wrong. In a job above you could then decide with path to take.

    Regards,
    Sven
    Last edited by sboden; 08-28-2007 at 12:47 PM.

  3. #3
    Join Date
    Jul 2007
    Posts
    2,498

    Default

    What you could do in 3.0 is:
    Isn't what you said exactly the same for 2.5?
    Pedro Alves
    Meet us on ##pentaho, a FreeNode irc channel

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

    Default

    Quote Originally Posted by pmalves View Post
    Isn't what you said exactly the same for 2.5?
    Sure ... I misread the original mail assuming he wanted to have the timestamp of the file itself. This is something that can be done with get files step in 3.0, but not yet in 2.5.

    Regards,
    Sven

  5. #5
    Join Date
    Aug 2007
    Posts
    4

    Default

    To be more clear , f1's date_extract field value is a column in the txt file itself so it's not the timestamp of the file.


    Sven,

    Could you explain more your idea particularly how to make the cartesian join to get one row with the two dates and also how you filter the two dates then how to use the result of tthis comparaison in a job.

    Thanks in advance.
    Best Regards
    boulanou

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

    Default

    Example attached.... 1 used 2 row generators, but you can get 1 row from file and 1 row from a database... I also use numbers in this examples but it should also work with dates (if you get the locale right)

    Regards,
    Sven
    Attached Files Attached Files

  7. #7
    Join Date
    Aug 2007
    Posts
    4

    Default solution found!

    Thank you sboden.

    I have implemented the loading status table with your example and it works.

    Thank you all also for your answer.
    Best Regards
    boulanou

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.