Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Comparing data of two tables A & B and update tableB

  1. #1
    Join Date
    Aug 2009
    Posts
    2

    Default Comparing data of two tables A & B and update tableB

    Hello Friends,
    i am very new in Pentaho Data Integration and need help.

    Problem: We have two Tables. Table1 (id,name,city) have to compare with Table2 (id,name,city).
    If Table2 have new city value, it means data has been changed and Table2 must be updated with the existing and new fields (id,name,newcity,validfrom,validtill).

    Which steps i must use? How to accomplish this task? Any type of help will be highly appreciated.

    Thanks in advance
    Adnan

  2. #2
    Join Date
    Sep 2007
    Posts
    834

    Default

    Read each table with a Table Input step. Then use the Merge Rows (diff) step. The step will compare the streams and give you back the differences.

    http://wiki.pentaho.com/display/EAI/Merge+rows

  3. #3

    Default

    use the table input and modify the sql script,such as 'select b.id ,b.city as new city from B,A where B.id=A.id ' and then use insert/Update step

  4. #4
    Join Date
    Aug 2009
    Posts
    2

    Default

    Hi,
    Thanks a lot for your reply. After using Merge Rows (diff) on Table_A and Table_B i am getting the desire Output with fieldflags like (new, deleted, identical) in Table_C.

    Requirement: All the data must be store again in Table_A and Date field must be change as a current date.

    Problem: after giving Table_A as a target only the new data is coming.
    How to solve it and how to change the date as a current date in Target table?

    Many Thanks
    Adnan

  5. #5
    Join Date
    Aug 2009
    Posts
    14

    Default

    You should use step "synchronize after merge" for updating the table.
    For the date maybe another step merge or something is needed. Dont know exactly what you want.
    But synchronize after merge is definitly the step you need.

  6. #6

    Default

    you use database lookup table after that update tools u can use for this purpose

  7. #7
    Join Date
    Jan 2014
    Posts
    9

    Default Comparing Data Issue.

    Quote Originally Posted by rushi.nasre@gmail.com View Post
    you use database lookup table after that update tools u can use for this purpose

    Hi,
    I am new to Pentaho DI and need help on a issue.

    I have an excel input which i need to insert into a table(MySQL database). But before insertion i want to compare the excel sheet and the databae table based on a date column present in both.

    For Example:- if the the excel sheet has data for the dates 01-01-2014,02-01-2014 and the DB table has the same data, then the table data with the dates 01-01-2014,02-01-2014 must get truncated and the complete data from the excel sheet must be loaded into the database table.
    I hope i am clear enough.
    Please help me out with this. What steps should i use ?

    Thank You in advance.

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.