TableJoin Transform

image_pdfimage_print

The TableJoin type joins two data sources via one or more key fields. It’s possible to join different type of sources with data coming from different source systems, e.g. flat files and relational databases.

Main settings:

The Join type must first be selected.

These are:

inner: The inner type joins data sets from the left-hand and right-hand tables precisely when all the criteria entered are fulfilled.
left outer: The left outer type declares the left-hand table the superior table. Even those lines which have no counterpart in the joined table appear in the results table. Fields which are not present in the right-hand table are filled with NULL.
cross: Cross join is the cartesian product of both sources. It is equivalen to an inner-join with no key columns.

Subsequently, the data sources to be joined are selected and the mapping keys defined for the respective data source. The tables are joined via these keys.

Join type

inner

Data source #1

Products

Data source #2

ProductCategoriesAll

Tree format #1

 

Tree format #2

 
Join key of source #1… … with key of source #2 Join condition
ProductCategorieID ProductCategorieID  

The number of join keys in the left and right data sources must be identical. The order of the two data sources is only important for the join type “left outer”.

The join condition can be: (Default is “eq”)
eq: equal (=)
ne: not equal (<>)
ge: greater equal (>=)
gt: greater (>)
le: lower equal (>=)
lt: lower (>)

Other join types:

  • The join type right outer can be achieved by using “left outer” and inversing the order of sources.
  • The join type full join (full outer) is not supported.

Advanced settings:

The internal Jedox Integrator processing of the Table Join is controlled with option “Persisted Join” (the result is identical):

not set: The right join source is cached in memory during processing. This leads in general to a higher performance. For an inner join the source with less rows and columns should be chosen as right source.
set: Both join sources are stored in an embedded database during processing. It should only be used, if both sources have a huge number of columns and rows which would lead to memory problems. For join conditions different to “eq” the Persisted Join variant is used independent of this setting.

Additional setting “Right source is unique”:

not set: The join is calculated completely.
set: In case of a Persisted Join, this option can be set if the right join source only contains each key value only once. In case of a huge right source this leads to an increase of performance.

Additional setting “All output columns”:
This setting describes the behavior if the left and the right source contain equally named columns.

not set (default): Only the columns of the left source are put to the output structure. A warning message is raised in this case.
set: Both columns are put to the output structure, the corresponding columns of the right source are suffixed with an index.
image_pdfimage_print
Was this post helpful?
NoYes (+2 rating, 2 votes)
Loading...