Liquid XML Studio 2019
Join
Data Mapping > Data Mapper Functions > By Category > Misc > Join

Function Name
Join
Category
Misc
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>

 

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.