DSXchange

Hi all,


Can anyone of you help me out with the logic for the following

• Col1-------col2-----Col3
  Header1--- X------ 0
  Detail1 ----D---------1
  Detail1------D------ - 2
  Detail1----- D---------3
  Detail1------C---------4
  Detail1------D------ --5
  Header2----X---------0
  Detail2------D--------1
  Detail2------C--------2
  Detail2------D--------3
  Header3----X--------0
  Detail3------D--------1
  Detail3------C--------2
  Detail3----- D--------3
  
  The above one is the input And Output should look like
  
  
  Col1-----------------Col2
  Header1-------------11(sum of all detail values in col3 with col2 = D )
  Detail1a -------------1
  Detail1b-------------- 2
  Detail1c---------------3
  Detail1d---------------4
  Detail1e---------------5
  Header2--------------4(sum of detail2 values with col2 = D)
  Detail2a----------------1
  Detail2b----------------2
  Detail2c----------------3
  Header3----------------4(sum of all detail3 values with col2 = D)
  Detail3a----------------1
  Detail3b----------------2
  Detail3c----------------3
  Can anyone help me how to do this!!
  
  THanks!
  Marcus

Have you tried the Aggregator stage? You may need to split out the data that you want to sum and merge them back. Read the manual on the Aggregator stage and have a play with it. There is option to SUM the columns.

Thanks for the reply. I tried using the Aggregator stage and group by option but am unable to generate the required output.

Will the records come in same order???

what exactly did you use in Aggregator

If the records come in same order, then you can use a stage variable stgvar in transformer and reset the stage variable stgvar to zero if col2='x' else if col2='D' add col3 to stgvar, else if col2<>'D' or 'x' add zero to stgvar.

Aggregate the detail and prepare the header in file1.

Add to detail rows and order them to create the structure.

Alternatively, read the rows in reverese order and do a control break.

Use a copy to stage to duplicate the detail lines.

In one stream aggregate the detail on the key columns (columns 1 and 2) This will give you a total for each of the combinations.
eg Detail 1|D = 11; Detail 1|C = 4; Detail 2|D = 4 Detail 2|C = 2 etc.
Use a transform to select only the total values that you are interested in ie where col2 = D. Update the output columns to contain the required header value (I assume here that there would be a logical way to link it back the to detail lines) and the total value.

In the other stream - manipulate the column values to the requirements

Funnel and sort the 2 streams.

There are 45 other columns other than the two above so cannot use an aggregator

Grace J. wrote:If the records come in same order, then you can use a stage variable stgvar in transformer and reset the stage variable stgvar to zero if col2='x' else if col2='D' add col3 to stgvar, else if col2<>'D' or 'x' add zero to stgvar.

Wish it were that easy. I just gave the value of header as '0' just to simplify. The header actually has a value which is not zero( sum of all Detail's col3, I am redoing the header so as only to contain the sum of all Detail's col3 with only col2 = D ).

Two good examples of the problem with leaving out information regarding your issue or with providing a 'simplified' example. People take the time to respond and their responses are invalid because they didn't have all of the facts to work with.

Sorry for the mess.! Criag, from the next time I will try to post my queries as detail as possible!

Marcus426 wrote:There are 45 other columns other than the two above so cannot use an aggregator

Why not?

Just split the other 45 columns (along with key of course) and join them back the other side of the aggregator

Right, that would be the 'fork join' design that John Smith mentioned in the first reply here.