Results 1 to 3 of 3

Thread: Join when there are no matching fields

  1. #1

    Default Join when there are no matching fields

    Hello,
    Is there a way to do an outer join when there are no matching fields in the tables?
    I want to get the cartesian product out of the join.
    For example
    Table A contains the field year with the values 2014 and 2015.
    Table B contains the field quarter with the values Q1, Q2, Q3 and Q4

    What I would like is to join the tables and get the following:
    Year,Quarter
    2014, Q1
    2014, Q2
    2014, Q3
    2014, Q4
    2015, Q1
    2015, Q2
    2015, Q3
    2015, Q4

    So far I have not found a way to do this.
    Is this possbile and how do you do it?

    br
    Martin

  2. #2
    Lavastorm Employee
    Join Date
    Dec 2006
    Location
    Dallas, TX
    Posts
    297

    Default

    You'll need to put something in the LeftInputKey and RightInputKey parameters. Put a constant value in them, the same value in both so that they match, like for example a 1 or a string expression like "x".

  3. #3
    Lavastorm Employee
    Join Date
    Apr 2014
    Location
    London
    Posts
    57

    Default

    Hi Martin,

    I've attached a graph with examples using the X-Ref and Inner Join nodes, both work in your scenario. If you're using a constant value then you don't really need to see orphan data using the X-Ref node but we would recommend using the X-Ref when working with new data sets as this can easily identify data quality issues. In a production environment the Inner Join node is more efficient where you know that there won't be any left/right orphans.

    Join Data with No Match.brg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •