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!!
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.
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.
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
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
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.