Technical Articles
Repeat Rows N Times according to Column Value in BODS
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: