summary and detail record logic in the same target

[arvind]

Hello Everybody,

I need to create a file with summary and detail records.

Source file contains only the detail records, ftp stage will read the data from the source, need do sort and group the detail records based on 2 columns.
Need to create a summary record for every group.
In Target I will have both summary and detail records (all groups of detail records in sorted order).
Eg:
Source
col1 col2 col3 col4 col5 col6 col7 col8
A1 IND 10132005 123 56 bcd abc 12.0
A1 US 10052005 453 98 khl mnh 9.00
A1 JPN 10062005 234 54 lkj hgf 89.9
A1 US 10052005 786 32 sdf lkj 98.0
A1 JPN 10062005 783 65 eda lsg 76.0
A1 IND 10092005 456 12 abd cde 45.9

Target
Col1 col2 col3 col4 col5 col6 col7 col8
A2 IND rundate 579 startdate enddate
A1 IND 10132005 123 56 bcd abc 12.0
A1 IND 10092005 456 12 abd cde 45.9
A2 US rundate 1239 startdate enddate
A1 US 10052005 453 98 khl mnh 9.00
A1 US 10052005 786 32 sdf lkj 98.0
A2 JPN rundate 1017 startdate enddate
A1 JPN 10062005 234 54 lkj hgf 89.9
A1 JPN 10062005 783 65 eda lsg 76.0

In the target I will have the summary record for each group (IND,US,JPN),
Groups are created based on col2 and col3.
Col4 value in the summary record is sum of the values of col4 of that group.
Please suggest me how to solve the above logic.

Thanks in Advance
Arvind

[ameyvaidya]

one possible solution:

Calculate/Pass the detail data and your calculated summary in 2 different streams.
In a transformer stage x-1D(Detail), Consolidate every detail column but Col1 into the required output format and pass it a a single Column(RowData longvarchar(4096)). Add a generated integer Column( Set it to any non zero Positive Number).

In another transformer stage x-1S(Detail), Consolidate every summary column but Col1 into the required output format and pass it a a single Column(RowData longvarchar(4096)). Add a generated integer Column( Set it to zero).

In Funnel Stage x, consolidate all detail and summary rows.

In Sort Stage x+1 running in sequential mode(IMPORTANT), sort on key1=Col1;key2=<generated Integer column>

In Transformer X+2 also running in Sequential Mode, Combine the Col1 column with the RowData column to get the complete output row format.

Use a sequential file stage x+3 to output this file(WITHOUT ANY QUOTES OR DELIMITERS).

HTH,

[ameyvaidya]

In another transformer stage x-1S(Detail),

Please read as

In another transformer stage x-1S(Summary),