This discussion is archived
4 Replies Latest reply: Jul 25, 2013 1:59 AM by MikeMatthews RSS

Join two tables - column merge

balajivenkat Newbie
Currently Being Moderated

Table1 - Product ID, Description

Table 2 - Product ID, Cost

 

I want the result to contain Product ID, Description and Cost. Which process should I use?

 

Balaji

  • 1. Re: Join two tables - column merge
    MikeMatthews Pro
    Currently Being Moderated

    Define a lookup against the table you want to check against. (Right-click on Reference Data node in the project browser, Add Lookup). The lookup can be to either staged data or external data (if the data is dynamic)

     

    Then add a Lookup and Return processor to your process and configure the options. You can check for the correct cardinality if required.

     

    If you are doing a 1:M lookup and want to create a 'join' in the process, input all the array attributes from Lookup and Return into a single Split Records from Array processor and then either Write or do other downstream processing on the joined stream.

  • 2. Re: Join two tables - column merge
    MikeMatthews Pro
    Currently Being Moderated

    Alternatively, you could just snapshot a view, or use SQL in the snapshot.

  • 3. Re: Join two tables - column merge
    balajivenkat Newbie
    Currently Being Moderated

    Thank you very much Mike. I was able to join 2 tables using 'Lookup and Return' and 'Split records from Array' processors. Is it possible to join more than two tables at once using these two processors?

  • 4. Re: Join two tables - column merge
    MikeMatthews Pro
    Currently Being Moderated

    Generally, I would say use multiple processors for this - one for each link between tables. From a DQ perspective this is better as the lookup checks the relationship as it processes.

     

    It is possible to use SQL in a snapshot though and lookup onto this which would enable the use of a single processor, or even no processors if all you are trying to do is bring the data in rather than check consistency etc.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points