Creating hierarchical outbound file

[biswajit_paul]

Hi,

I have to generate an outbound file with the folllowing hierarchical structure.
This file has Invoice and invoice line item records per Source Application Accronym(SAA).
The structure of the file is:
SAA1
Header1
Invoice 1
Line Item 1
Line Item 2
Line Item 3
Invoice 2
Line Item 1
Line Item 2
Trailer1 9
SAA2
Header2
Invoice 3
Line Item 1
Line Item 2
Line Item 3
Invoice 4
Line Item 1
Line Item 2
Trailer2 9

The header will have a sequence number incremented by 1 for each header record written.

The trailer will have a sequence number incremented by 1 for each triler record written.
It will also have a record count within the header/trailer group.

How can I achieve this in PX?
I have DataStage/PX Version 7.

Can anyone please help?

[vmcburney]

It's not something that is standard DataStage functionality. The product prefers to flatten out complex data structures rather then the other way around. This would be extremely easy to do if you target was an XML file. This might sound silly but one option is to write it to an XML file with very short tag names and then use a Unix script to strip out all the XML tags.

The design depends on how your data is being read by the job. If your source data is in a relational database where invoice lines and header lines are in different tables one option is to have a job with up to four inputs that merge all four using a link collector. You would need to embed some ordering fields into the selection of rows in order to keep the output in the correct order.

[alokkum]

I tried to understand what you are trying to achieve using an ETL tool. This is a report kind of structure which you are trying to get using DataStage. I do not say it is impossible, but please note that Datastage is not meant to produce reports, inturn it does ETL. You are trying to present a report thru ETL. There are many reporting tools in the market which do this in a very formatted way and they do filter and join seamlessly. To do the same using ETL will definitely not be a good choice. Still if you intend yto do this, it will not be an easy build.

Thanks,
Alok