summary and detail record logic in the same target
Posted: Wed Oct 12, 2005 3:36 pm
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
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