Hi,
We have an input file in the format:
Col1 Col2 Col3
A B aaa
A B bbb
A B ccc
A B ddd
A B eee
C D fff
E F ggg
E F hhh
E F iii
E F jjj
We have to split the records into 2 files when the number of records based on the key (Col1, Col2) is greater than or equal to 3.
So, the requirement is to have 2 output files for the above input:
File1:
A B aaa
A B bbb
A B ccc
C D fff
E F ggg
E F hhh
E F iii
File2:
A B ddd
A B eee
E F jjj
Please suggest options/ solution to accomplish this output.
Please Note: We need to split the records because of SAP's limitation for the number of line items set to 999 for a given invoice. (And the output files would actually be IDocs)
Thanks!
How to split records
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 301
- Joined: Thu Jul 14, 2005 10:27 am
- Location: Melbourne, Australia
- Contact:
So, if I understand correctly, you want the first 3 rows of a Col1/Col2 key par to go to File1, and the remainder to File2? If so, try the following ...
Use a Sort stage to sort your input by Col1 and Col1. Add a 'KeyChange' column (generated by the sort stage) to indicate a change in Col1/Col2 values.
Follow with a transformer which uses a stage variable to keep a count of the number of rows received for the current Col1/Col2 combination. The derivation will be something like ...
svKeyCount = If KeyChange Then 1 Else svKeyCount + 1
You will have two output links, File1 and File2. Contraints should be:
File1: svKeyCount <= 3
File2: svKeyCount > 3
Good Luck.
J.
Use a Sort stage to sort your input by Col1 and Col1. Add a 'KeyChange' column (generated by the sort stage) to indicate a change in Col1/Col2 values.
Follow with a transformer which uses a stage variable to keep a count of the number of rows received for the current Col1/Col2 combination. The derivation will be something like ...
svKeyCount = If KeyChange Then 1 Else svKeyCount + 1
You will have two output links, File1 and File2. Contraints should be:
File1: svKeyCount <= 3
File2: svKeyCount > 3
Good Luck.
J.
<b>John McKeever</b>
Data Migrators
<b><a href="https://www.mettleci.com">MettleCI</a> - DevOps for DataStage</b>
<a href="http://www.datamigrators.com/"><img src="https://www.datamigrators.com/assets/im ... l.png"></a>
Data Migrators
<b><a href="https://www.mettleci.com">MettleCI</a> - DevOps for DataStage</b>
<a href="http://www.datamigrators.com/"><img src="https://www.datamigrators.com/assets/im ... l.png"></a>
-
- Premium Member
- Posts: 301
- Joined: Thu Jul 14, 2005 10:27 am
- Location: Melbourne, Australia
- Contact:
Glad you got it working.
You might want to set this topic to 'Resolved'.
You might want to set this topic to 'Resolved'.
<b>John McKeever</b>
Data Migrators
<b><a href="https://www.mettleci.com">MettleCI</a> - DevOps for DataStage</b>
<a href="http://www.datamigrators.com/"><img src="https://www.datamigrators.com/assets/im ... l.png"></a>
Data Migrators
<b><a href="https://www.mettleci.com">MettleCI</a> - DevOps for DataStage</b>
<a href="http://www.datamigrators.com/"><img src="https://www.datamigrators.com/assets/im ... l.png"></a>