Repeat Rows N Times according to Column Value in BODS

[former_member790041]

Today I'm going to tell you how to create repeated rows based on value of specific column.

Input

The input contains Workers details for a task in the given period of timings/shift.

Output

The Output dataset must display individual workers for a combination of timings and task as shown below.

Solution

We can follow the below steps to get repeated rows.

Dataflow Creation

 

Row Generation Transform

• The Row Generation transform does nothing else than generating one row after the other with one INTEGER column only.


• Row Generation transform produces a data set with a single column. The column values start with the number that we specify in the Row number starts at option.


• The value then increments by one to specified number of rows as set in the Row count option. This transform does not allow any input data set.

Finally, we use the below join conditions between source file and row generation output to repeat the records.

The above method is based on the following SQL query:

SELECT  t1.*, t2.number + 1 RepeatNumber

FROM    TableA t1

JOIN    master.dbo.spt_values t2 ON t2.type = 'P' AND t2.number < t1.Quantity

Note for master.dbo.spt_values on type = 'P':

This table is used for getting a series of number which is hard-coded in it by

condition of type = 'P'.

Conclusion

Hope you have learnt how to create repeated rows based on a column value in SAP Data Services, this will help in various Data Optimizer projects, for more such interesting content, please follow my profile and blog, will be sharing many more technical articles in coming days.

For any questions or queries, please use Q&A and provide your feedback in the comment section.

Refer below tag for more interesting content in Data Services:

https://blogs.sap.com/tags/01200314690800000395/