SAP BODS - Concatenate Custom Function

[Former Member]

SAP Business Objects Data Services (DS) does not provide the functionality to concatenate multiple row entries into a single row, where the number of rows that might appear is undefined.

Description:

The Data Services have the functionality to concatenate multiple rows into a single row entry using the reverse pivot transform and then concatenating the columns provided that the number of rows that need to be concatenated is pre- defined or fixed.

If the number of rows that might appear which needs to be concatenated is not defined then the same functionality can be achieved at the database level through SQL using the XML functions. The XML functions XMLAGG and XMLELEMENT are used to concatenate the multiple entry rows into a single row. XMLELEMENT will take an element name for identifier and returns an instance of XML type. While XMLAGG takes a collection of XML fragments and returns an aggregated XML document. E.g.(xmlagg(xmlelement(e,<column_to_be_concat>,',').extract ('//text()')),'<delimiter>')

Using the XML functions at the database level will affect the performance of the jobs. For millions of records to be processed the usage of XML function would consume lot of time and memory space which would reduce the performance of the jobs.

Solution:

A DS Custom Function can be used to concatenate the multiple rows into a single row even when the number of row sets that might appear for a set is not defined.

Advantages:

1. 1.     The set of rows that must be concatenated need not be defined.
2. 2.     Improved Job performance.
3. 3.     Memory space used for cache will be low.
4. 4.     Easy implementation of the function.

Input Data:

The source table has four columns namely unique_id, name, Text and Note_seq_num. The source information has the data set like

Unique_id	Name	Text	Note_Seq_num
1	John	Hello	1
1	John	World	1
2	Rob	How	2
2	Rob	Are	2
2	Rob	You	2

Requirement:

For each of the distinct Unique_id and name we should have only a single entry in the Target table and the Text column should be concatenated. The Note_seq_num column is used as reference column to identify the set of rows that needs to be concatenated based on the corresponding unique_id and Name.

The sample output should be like

Unique_id	Name	Text
1	John	Hello,World
2	Rob	How,Are,You

Concatenation Custom Function:

The Custom function uses the column Unique_id (the primary column based on which the row are to be concatenated) and the Note_seq_num.

Pre-Requisites:

1. 1.     The Note_seq_num should be generated for every group of data(if it is not present in the data set), which identifies those records which need to be concatenated.
2. 2.     For each unique_id, the note_seq_num should be sorted.
3. 3.     The Global Variables should be created which would be used for the custom function.

$G_VAR_NOTE_SEQ_NUM and $G_VAR_TEXT – variable will hold the Note_seq_num value and concatenated Text column during the time of execution of the custom function

Create the Custom Function:

After the execution of the custom function, the output would be as shown below:

Unique_id	Name	Text
1	John	Hello
1	John	Hello,World
2	Rob	How
2	Rob	How,Are
2	Rob	How,Are,You

Post Custom Function Execution:

The resultant output can be obtained by grouping the records based on unique_id and Name and by getting the Max of Text column.

Unique_id	Name	Text
1	John	Hello,World
2	Rob	How,Are,You