Joins
Joins
Join Rows (Cartesian product)
Description
Join Rows is a step in the Joins Plugin for Process Studio Workflows. The Join rows step creates combinations (Cartesian product) of all rows in the input streams. The number of rows is the multiplication of the number of rows in the input streams.
Configurations
No.
Field Name
Description
1
Step name
Name of the step; this name has to be unique in a single workflow
2
Temp directory
Specify the name of the directory where the system stores temporary files. This is required when you want to combine more than the cached number of rows.
3
TMP-file prefix
Specify the prefix of the temporary files that will be generated.
4
Max. cache size
Specify the maximum cache size. Only after the cache size threshold the system uses temporary files for caching data. This is required when you want to combine large row sets that do not fit into memory.
6
Main step to read from
Specify the step from which to read most of the data. The data from this step is spooled to the temporary file in disk while the data from other steps is not spooled to the file.
7
The Condition(s)
You can enter a multi-level condition to limit the number of output rows.
Merge Join
Description
Merge Join is a step in the Joins Plugin for Process Studio Workflows. Merge Join step is used to merge data sets coming from two input streams with a common key column. It can join the two streams on a given key as an Inner Join, Left Outer Join, Right Outer Join or Full Join and outputs a joined set with fields in the two sets. The input streams must be sorted on the join key.
Configurations
Fields Tab
No.
Field Name
Description
1
Step name
Specify the name of the step. The step name should be unique within the workflow.
2
First Step
Used to specify first input step to the merge join (left).
3
Second Step
Used to specify second input step to the merge join (right).
4
Join Type
Select the type of Join.
- NNER-Only rows having the same key in both datasets are included in the result.
- LEFT OUTER-All rows from the first dataset are included in the result, along with empty values for non-matching keys in the second dataset.
- RIGHT OUTER- All rows from the second dataset are included in the result, along with empty values for non-matching keys in the first dataset.
- FULL OUTER- All rows from both datasets are included in the result, along with empty values for non-matching keys in both datasets.
5
Keys for 1st step
Used to specify key fields on which the incoming data is sorted.
6
Keys for 2nd step
Used to specify key fields on which the incoming data is sorted.
Buttons: | ||
No. | Field Name | Description |
1 | Get key fields | When clicked, it retrieves a list of fields from the step specified. |
Merge Rows (diff)
Description
Merge Rows (diff) is a step in the Joins Plugin for Process Studio Workflows. Merge rows step compares data from two input streams (say reference and compare rows) and merges the data, based on common keys. The data from both the input steps should be sorted on the key used for merging. The two streams are compared based on keys as well as other fields of the row. The latest version of a row is passed to the next steps. The new rows are flagged as equals, changed, new or deleted as the case may be. (This is useful for comparing data from two different times. It is often used in situations where the reference system does not contain a date of last update).
This step may be used in conjunction with Synchronize after merge step. The flag column is then used to execute updates/inserts/deletes on a target table.
Configurations
No.
Field Name
Description
1
Step name
Specify the name of the step as it appears in the workflow workspace. This name has to be unique in a single workflow.
2
Reference rows origin
Specify the step with reference rows.
3
Compare rows origin
Specify the step with compare rows (new rows).
4
Flag fieldname
Specify the name of the flag field on the output stream. It will contain one of the following values: Identical, Changed, New or Deleted
5
Keys to match
Specify fields containing the keys on which to match; click Get key fields to insert all of the fields originating from the reference rows step
6
Values to compare
Specify fields containing the values to compare; click Get value fields to insert all of the fields from the originating reference and Compare rows steps. Key fields need not be specified here. The Flag fieldname is populated depending on the value in the compare field or the absence of rows for some keys in the compare rows the
Multiway Merge Join
Description
Multiway Merge Join is a step in the Joins Plugin for Process Studio Workflows. This step allows joining of multiple streams and supports INNER and FULL OUTER joins. This is in contrast to the Merge Join step only allows doing this for two streams.
Configurations
No.
Field Name
Description
1
Step name
Name of the step as it appears in the workflow workspace. This name has to be unique in a single workflow.
2
Join type
List of Values: INNER, FULL OUTER
Sorted Merge
Description
Sorted Merge is a step in the Joins Plugin for Process Studio Workflows. The Sorted Merge step is similar to the Merge Join step in that it merges rows coming from multiple input steps. The differentiator is that that these rows are also sorted on the given key fields in the step itself.
Configurations
No.
Field Name
Description
1
Step name
Name of the step as it appears in the workflow workspace. This name has to be unique in a single workflow.
2
Fields table
Specify the field name and sort direction (ascending/descending); click Get Fields to retrieve a list of fields from the input stream(s).
XML Join
Description
XML Join is a step in the Joins Plugin for Process Studio Workflows. This step can join a stream of XML-Tags into a target XML string. The target stream must have only one row, since it represents an XML document. The source stream can consist of many rows and the tags from all rows are added to the target document. After the join only one row will be produced containing the fields of the target step plus the result field of the join.
Configurations
No.
Field Name
Description
1
Step name
Name of the step as it appears in the workflow workspace. This name has to be unique in a single workflow.
Target Stream Properties:
2
Target XML Step
Specify the step that sends the target document to the join
3
Target XML Field
Specify the field that contains the XML structure
Source Stream Properties:
4
Source XML Step
Specify the step that send the XML structure(s) to the join that need to be added to the target
5
Source XML Field
Specify the field that contains the XML structures that get added to the target
Join Condition Properties:
6
XPath Statement
Specify the XPath statement to find the node in the target document where the tags will be added.
When complex join is enabled a single? is used as a placeholder.7
Complex Join
Enable the complex join syntax, using the placeholder in the XPath Statement
8
Join Comparision Field
Specify Field that contains the values that get replaced in the XPath Statement
Result Stream Properties:
9
Result XML field
Specify the field that will contain the result.
10
Encoding
Choose the encoding to be used in the XML Header and to transform the XML.
11
Omit XML header
Enable checkbox to omit XML header.