Header, Detail design issue

[lfong]

I have an input file with a header and 3 types of detail records.
I have split the header and 3 detail records using a filter stage, then into transforms. I need to get the date from the header record in the 3 transformed detail records.
What would be the best way to do this?

Thanks

[boxtoby]

Try using a Stage Variable to hold the Header Date and in the derivation check for @INROWNUM = 1 or record type of Header so you only update the Stage Variable once.

The Stage Variable can then be used to populate the column in the detail records.

You may also need to control the parallel processing in that the Header may be handled by one node and therefore detail records being handled by the other nodes may not see it. Might have to force sequential processing in to the first transformer to get the date and the do the split in a subsequent transformer.

[mhester]

If the header and 3 detail have the same key you could do a join or merge or even a simple lookup to handle this situation.

I would refrain from using stage variables and messing with what node is processing what record etc... not a good way to handle the logic.

You could load the headers into a lookup file set, sequential file or data set and then stream your detail rows and join on the headers and grab what you need.

Also, the filter stage is not as efficient as the transformer stage for doing things like splitting into various output streams.

[lfong]

Mhester, I have to use either a filter or switch because the header and 3 detail records each have their own definition.
Boxtoby, aren't stage variables local to the transform being used? I have 4 transforms after the filter.

[mhester]

Fine - use the filter, but don't do the stage variables.

[JoshGeorge]

Pre-process to pick the date part from header in file and pass the date as a job parameter.