Generate detail and summary data in a file
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 43
- Joined: Wed Feb 27, 2008 1:08 am
Generate detail and summary data in a file
Hi,
I have to get data from an oracle table and generate a text file in the following way. I have to print detail records based on aggregated column and then summary and again detailed records and summary column. If you see below sample output file,you can understand what need to be done.
sample.txt
Company1|20081001|500.00
Company1|20081001|1000.00
Company1|20081001|300.00
Company1|20081001|1800.00 - summary record for above 3 rows
Company2|20081001|1500.00
Company2|20081001|1000.00
Company2|20081001|2500.00 - summary record for above 2 lines
------------
------------
------------ and so on.
All data comes from a single table. Could somebody let me know how we can achive this using parallel jobs ? I know how to get details and how to get summary as different files but don't know how to get in the above format.
Thanks in advance for your help.
Raj.
I have to get data from an oracle table and generate a text file in the following way. I have to print detail records based on aggregated column and then summary and again detailed records and summary column. If you see below sample output file,you can understand what need to be done.
sample.txt
Company1|20081001|500.00
Company1|20081001|1000.00
Company1|20081001|300.00
Company1|20081001|1800.00 - summary record for above 3 rows
Company2|20081001|1500.00
Company2|20081001|1000.00
Company2|20081001|2500.00 - summary record for above 2 lines
------------
------------
------------ and so on.
All data comes from a single table. Could somebody let me know how we can achive this using parallel jobs ? I know how to get details and how to get summary as different files but don't know how to get in the above format.
Thanks in advance for your help.
Raj.
-
- Premium Member
- Posts: 43
- Joined: Wed Feb 27, 2008 1:08 am
Hi, But in that case we get all details and then all summaries right. I want details then summary pertailing to that details again all details and then one summary and so on.mystuff wrote:split into two paths, use aggregator for one path and the other for details. Finally use funnel stage.
Although I usually prefer to do both unloads from database itself and then combine. Wouldln't recomment aggregator for huge data.
I want data in this format
company1 detail record 1
company1 detail record 2
company1 detail record 3
company1 summary record
company2 detail record 1
company2 detail record 2
company2 detail record 3
company2 summary record
company3 detail record 1
company3 detail record 2
company3 summary record
Summaries are supposed to come in between but not together at the starting or ending.
Thanks a lot
I've done this by assigning a 'group id' to each set of records and a code for 'detail' versus 'summary' then carrying that forward. Then after you've split and summed, you can use those codes to get everything in the proper order by sorting after you funnel everything back together.
Lastly, you can simply drop the codes you've added to the stream before writing out the results as they've served their purpose. Seems like you could use that 'company number' as the initial grouping id and retain it, though.
Lastly, you can simply drop the codes you've added to the stream before writing out the results as they've served their purpose. Seems like you could use that 'company number' as the initial grouping id and retain it, though.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 43
- Joined: Wed Feb 27, 2008 1:08 am
Hi Craig,chulett wrote:I've done this by assigning a 'group id' to each set of records and a code for 'detail' versus 'summary' then carrying that forward. Then after you've split and summed, you can use those codes to get ...
Thanks a lot for your responce
Could you explain me in detail (in 5 to 6 lines) on how you generated group and detail codes, what and all stages you have used and finally how you used codes to generated file. Did you have to use any staging tables ?.
Thanks again.
RAJ
No staging required. Codes are simple, you've got a ready made 'group id' in the company number, just add a 'D' or 'S' code and they'll sort back together into the proper order after the aggregator and funnel. Drop the code after that as it has outlived its usefulness at that point.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Cant we use sort funnel ??
rajadommeti wrote:Hi, But in that case we get all details and then all summaries right. I want details then summary pertailing to that details again all details and then one summary and so on.mystuff wrote:split into two paths, use aggregator for one path and the other for details. Finally use funnel stage.
Although I usually prefer to do both unloads from database itself and then combine. Wouldln't recomment aggregator for huge data.
I want data in this format
company1 detail record 1
company1 detail record 2
company1 detail record 3
company1 summary record
company2 detail record 1
company2 detail record 2
company2 detail record 3
company2 summary record
company3 detail record 1
company3 detail record 2
company3 summary record
Summaries are supposed to come in between but not together at the starting or ending.
Thanks a lot
Nag
-
- Premium Member
- Posts: 43
- Joined: Wed Feb 27, 2008 1:08 am