One Star

Compare the fields between 2 tables

Hi,
I want to check the two tables row by row for each fields and get the insert/update/delete details for each and every fields. I have to generate a excel report using the values but the main problem area is I have to identify the updated field with old value and updated new value (both).

* How can i compare and get the changes between 2 tables.
* How can i get the exact change and the old value before the change in excel report.

Please let me know your pointers.

Thanks and Regards,
Manikandan.R
3 REPLIES
One Star

Re: Compare the fields between 2 tables

Hi Manikandan.R
This is a little tricky, but it will only work if you have a primary key on the tables:
1. Using tmap perform an inner join on old (main) and new (lookup) using every field as the join key. Discard matches and collect the inner join rejects into a file. The matches are unchanged rows and the rejects will be the rows.
2. Using tmap perform an inner join on the file from stage 1 (main) and new (lookup) using the primary key. Collect matches into a file and inner join rejects into another file. The matches will be updated rows and the rejects will be deleted rows.
3. Copy step 1, but this time use the new as main and the old file(not file from stage1) as lookup. There is no need to collect matches, just the rejects into a file. These rejects are inserted rows.
4. Now join the matches file from stage 2 (main) to the new table(lookup) using tmap. This will allow you access to the old (main) and new (lookup) fields for the updates. If you want to just include the fields that have changed then you can add a ternary expression to each field to copy the new value only if one exists e.g. old.col3.equals(new.col3) ? "" : new.col3. So you will then have a row that shows
I tested this with 3 columns and changed one row character from x to f and it gave a csv file like this:
key,col2_old,col2_new,col3_old,col3_new
d,e,,x,f
I'm not sure how your spreadsheet needs to look, but at this stage you will have: a file of deletes, a file of inserts and a file containing the update details field by field that you can then transform into whatever format you need.
Note that this won't work unless you have unique keys on the table.
And finally, you might be able to do this using the SCD component, since type 3 fields give old and new values, but I haven't really used this component.....a project for another day.
Hope this helps
Regards,
Rick
One Star

Re: Compare the fields between 2 tables

-- resolved --
Hi tchd,
i have the same problem...maybe..^^
There are two files (see attached file). I want that i get all new rows of the new file in comparison to
the old file and a second file that contains all similar rows with the changes. Can i do this with tMap?
In practice the old file ist not a excel sheet but a table in mysql...Ofcourse this dosen't matter for the exampleSmiley Very Happy
Thank you in advance!
Four Stars

Re: Compare the fields between 2 tables

hi,
i have table A that has "A_id" , "date" as columns and table B that has "B_id", "A_id", "Dates" as columns.
For each "A_id" there exists many "Dates".
I need to find the next date from "Dates" for a given "date" of A_id
I will explain with an example
Suppose consider the two tables 1, 2.
Table 1
            A_id         date
             2         2014/02/15
Table 2
            B_id        A_id           Dates
             1             2            2013/06/28
             2             2            2014/10/25
             3             2            2015/01/06
I need to get the next date corresponding to A_id=1 i.e,, 2014/10/25 from "Dates" Column
How do i do this using this talend?
Please help