Join

In This Topic

Function Name

Join

Icon

Description

Allows 2 sets of data to be associated with each other.

Inputs

Value

The value to be mapped

Outputs

Result

The mapped value

Properties

None

Usage

When we have 2 separate data sources that are related to each other, we can use the Join component to establish a relationship between them and construct a hierarchy.

In the following example we have 2 simple CSV data sets, one describing Album information, the other Tracks. They are related to each other via the Albums.AlbumID and the Tracks.AlbumID. By using a Join component we can build a hierarchy where an Album 'has' 0-n Tracks. We can then use this structured hierarchical data just as we would any other structured data source.

When we come to connect the Join component, the 'Table A' input will form the outer data (container), and 'Table B' the inner data (nested). On the output side a 'Track' connection point corresponding to 'Table B' will be added into the Album, corresponding with 'Table A'. When the value read from 'Table A -> Join Value 1' is equal to 'Table B -> Join Value 1' the Node read from 'Table B' will be available in the output, in this way its possible for an album to have 0-n tracks.

Albums.csv

AlbumID	Artist Name	Album Name	Publisher	Year
1	Nirvana	Nevermind	DGC Records	1991
2	Beatles	Abbey Road	Apple Records	1969

Tracks.csv

AlbumID	Track Number	Name	Length
1	1	Smells Like Teen Spirit	05:01
1	2	In Bloom	04:14
1	3	Come as You Are	03:39
1	4	Breed	03:03
1	5	Lithium	04:17
1	6	Polly	02:57
1	7	Territorial	02:22
1	8	Drain You	03:43
1	9	Lounge Act	02:36
1	10	Stay Away	03:32
1	11	On a Plain	03:16
1	12	Something in the Way	20:35
2	1	Come Together	04:19
2	2	Something	03:02
2	3	Maxwell's Silver Hammer	03:27
2	4	Oh! Darling	03:27
2	5	Octopus's Garden	02:51
2	6	I Want You (She's So Heavy)	07:47

Output : Library.xml	Copy Code
<!--Created by Liquid Data Mapper Libraries (www.liquid-technologies.com) for Liquid Technologies Ltd --> <Library> <Album> <Artist>Nirvana</Artist> <Album>Nevermind</Album> <Publisher>DGC Records</Publisher> <Published>1991</Published> <Track> <TrackNo>1</TrackNo> <Name>Smells Like Teen Spirit</Name> <Length>05:01</Length> </Track> <Track> <TrackNo>2</TrackNo> <Name>In Bloom</Name> <Length>04:14</Length> </Track> <Track> <TrackNo>3</TrackNo> <Name>Come as You Are</Name> <Length>03:39</Length> </Track> <Track> <TrackNo>4</TrackNo> <Name>Breed</Name> <Length>03:03</Length> </Track> <Track> <TrackNo>5</TrackNo> <Name>Lithium</Name> <Length>04:17</Length> </Track> <Track> <TrackNo>6</TrackNo> <Name>Polly</Name> <Length>02:57</Length> </Track> <Track> <TrackNo>7</TrackNo> <Name>Territorial</Name> <Length>02:22</Length> </Track> <Track> <TrackNo>8</TrackNo> <Name>Drain You</Name> <Length>03:43</Length> </Track> <Track> <TrackNo>9</TrackNo> <Name>Lounge Act</Name> <Length>02:36</Length> </Track> <Track> <TrackNo>10</TrackNo> <Name>Stay Away</Name> <Length>03:32</Length> </Track> <Track> <TrackNo>11</TrackNo> <Name>On a Plain</Name> <Length>03:16</Length> </Track> <Track> <TrackNo>12</TrackNo> <Name>Something in the Way</Name> <Length>20:35</Length> </Track> </Album> <Album> <Artist>Beatles</Artist> <Album>Abbey Road</Album> <Publisher>Apple Records</Publisher> <Published>1969</Published> <Track> <TrackNo>1</TrackNo> <Name>Come Together</Name> <Length>04:19</Length> </Track> <Track> <TrackNo>2</TrackNo> <Name>Something</Name> <Length>03:02</Length> </Track> <Track> <TrackNo>3</TrackNo> <Name>Maxwell's Silver Hammer</Name> <Length>03:27</Length> </Track> <Track> <TrackNo>4</TrackNo> <Name>Oh! Darling</Name> <Length>03:27</Length> </Track> <Track> <TrackNo>5</TrackNo> <Name>Octopus's Garden</Name> <Length>02:51</Length> </Track> <Track> <TrackNo>6</TrackNo> <Name>I Want You (She's So Heavy)</Name> <Length>07:47</Length> </Track> </Album> </Library>

Output : Library.xml

Copy Code

<!--Created by Liquid Data Mapper Libraries (www.liquid-technologies.com) for Liquid Technologies Ltd -->
<Library>
    <Album>
        <Artist>Nirvana</Artist>
        <Album>Nevermind</Album>
        <Publisher>DGC Records</Publisher>
        <Published>1991</Published>
        <Track>
            <TrackNo>1</TrackNo>
            <Name>Smells Like Teen Spirit</Name>
            <Length>05:01</Length>
        </Track>
        <Track>
            <TrackNo>2</TrackNo>
            <Name>In Bloom</Name>
            <Length>04:14</Length>
        </Track>
        <Track>
            <TrackNo>3</TrackNo>
            <Name>Come as You Are</Name>
            <Length>03:39</Length>
        </Track>
        <Track>
            <TrackNo>4</TrackNo>
            <Name>Breed</Name>
            <Length>03:03</Length>
        </Track>
        <Track>
            <TrackNo>5</TrackNo>
            <Name>Lithium</Name>
            <Length>04:17</Length>
        </Track>
        <Track>
            <TrackNo>6</TrackNo>
            <Name>Polly</Name>
            <Length>02:57</Length>
        </Track>
        <Track>
            <TrackNo>7</TrackNo>
            <Name>Territorial</Name>
            <Length>02:22</Length>
        </Track>
        <Track>
            <TrackNo>8</TrackNo>
            <Name>Drain You</Name>
            <Length>03:43</Length>
        </Track>
        <Track>
            <TrackNo>9</TrackNo>
            <Name>Lounge Act</Name>
            <Length>02:36</Length>
        </Track>
        <Track>
            <TrackNo>10</TrackNo>
            <Name>Stay Away</Name>
            <Length>03:32</Length>
        </Track>
        <Track>
            <TrackNo>11</TrackNo>
            <Name>On a Plain</Name>
            <Length>03:16</Length>
        </Track>
        <Track>
            <TrackNo>12</TrackNo>
            <Name>Something in the Way</Name>
            <Length>20:35</Length>
        </Track>
    </Album>
    <Album>
        <Artist>Beatles</Artist>
        <Album>Abbey Road</Album>
        <Publisher>Apple Records</Publisher>
        <Published>1969</Published>
        <Track>
            <TrackNo>1</TrackNo>
            <Name>Come Together</Name>
            <Length>04:19</Length>
        </Track>
        <Track>
            <TrackNo>2</TrackNo>
            <Name>Something</Name>
            <Length>03:02</Length>
        </Track>
        <Track>
            <TrackNo>3</TrackNo>
            <Name>Maxwell's Silver Hammer</Name>
            <Length>03:27</Length>
        </Track>
        <Track>
            <TrackNo>4</TrackNo>
            <Name>Oh! Darling</Name>
            <Length>03:27</Length>
        </Track>
        <Track>
            <TrackNo>5</TrackNo>
            <Name>Octopus's Garden</Name>
            <Length>02:51</Length>
        </Track>
        <Track>
            <TrackNo>6</TrackNo>
            <Name>I Want You (She's So Heavy)</Name>
            <Length>07:47</Length>
        </Track>
    </Album>
</Library>

Naming the Tables makes the transform much more readable.

Simply select the Join component and set the 'Table A Name' and 'Table B Name' properties in the property grid.

Composite Keys

If your data sets require more than one value to be matched in order to relate them, then you can add more Join Values by right clicking on them and selecting 'Add Above' or 'Add Below'.

Nested Joins

The example above demonstrates relating 2 data sets together, but it's possible to relate as many datasets as you need together in order to construct a hierarchical structure from many datasets. The following examples show how this has been done using extracts from the SQL Server NorthWind Sample Database.

Send Feedback