How to split records

[sundrop]

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!

[jhmckeever]

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.

[sundrop]

That worked! :D

Thanks

[jhmckeever]

Glad you got it working.

You might want to set this topic to 'Resolved'.