Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Compare two tables and return the column with difference

  1. #1
    Join Date
    Jul 2016
    Posts
    3

    Default Compare two tables and return the column with difference

    Hello
    I need to compare two tables, and return which column is different between tables.
    Table 1 -> Col 1 - Col2 - Col 3 ....
    line 1 -> A - B - C
    line 2 -> A - B - C


    Table 2 -> Col 1 - Col2 - Col 3 ....
    line 1 -> A - B - C
    line 2 -> A - D - C




    result
    Table 3 -> Col 1 - Col2 - Col 3 .... Diff
    line 1 -> A - B - C No
    line 2 -> A - D - C Yes - Col2, other columns that present a difference in values

  2. #2
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    A first step towards a generic solution would be to use Row-Normaliser - the result would be Table (line_number, column_name, value).
    Now you could join tables on (line_number, column_name).
    If you use User-Defined-Java-Expression to set Diff to the column_name in case of differing values, you'll be able to concatenate the names using Group-By.
    Let Group-By return all rows, so Row-Denormaliser can produce the final result.
    For a really generic solution you'll have to use ETL-Metadata-Injection, though.
    So long, and thanks for all the fish.

  3. #3
    Join Date
    Apr 2014
    Posts
    18

    Default

    Try Merge Rows(diff) step.

    Quote Originally Posted by rafa_bsi View Post
    Hello
    I need to compare two tables, and return which column is different between tables.
    Table 1 -> Col 1 - Col2 - Col 3 ....
    line 1 -> A - B - C
    line 2 -> A - B - C


    Table 2 -> Col 1 - Col2 - Col 3 ....
    line 1 -> A - B - C
    line 2 -> A - D - C




    result
    Table 3 -> Col 1 - Col2 - Col 3 .... Diff
    line 1 -> A - B - C No
    line 2 -> A - D - C Yes - Col2, other columns that present a difference in values

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.