Generate detail and summary data in a file

rajadommeti · Post by **rajadommeti** » Sat May 23, 2009 9:59 pm

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.

mystuff · Post by **mystuff** » Sat May 23, 2009 10:11 pm

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.

rajadommeti · Post by **rajadommeti** » Sat May 23, 2009 10:25 pm

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.

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.
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

chulett · Post by **chulett** » Sat May 23, 2009 10:39 pm

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.

rajadommeti · Post by **rajadommeti** » Sun May 24, 2009 7:02 am

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 ...

Hi Craig,
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.

chulett · Post by **chulett** » Sun May 24, 2009 7:41 am

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.

nagarjuna · Post by **nagarjuna** » Sun May 24, 2009 7:49 pm

Cant we use sort funnel ??

rajadommeti wrote:
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.
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.
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

chulett · Post by **chulett** » Sun May 24, 2009 8:22 pm

Is a 'Sort Funnel' something new in 8?

rajadommeti · Post by **rajadommeti** » Sun May 24, 2009 8:31 pm

chulett wrote:Is a 'Sort Funnel' something new in 8? ...

If i'm not wrong Sort funnel is a functionality in funnel stage

nagarjuna · Post by **nagarjuna** » Sun May 24, 2009 8:35 pm

sorry ..i mean to say one of the option in funnel ( sort , sequence , continous ).