Tuesday, October 2, 2012

Scenario 3 - Aggregator Transformation Example

Input :

Student NameSubject NameMarks
SamMaths100
TomMaths80
SamPhysical Science80
JohnMaths75
SamLife Science70
JohnLife Science100
JohnPhysical Science85
TomLife Science100
TomPhysical Science85

Output :

Student NameMathsLife SciencePhysical Science
Sam1007080
John7510085
Tom8010085


Answer :

Here the transformation which we have to use is Sorter and Aggregator .

Lets see the approach :

1) The Input from the Source Qualifier is sent into the Sorter transformation , And Sorted based on Student name and Subject.
The following output is obtained
Student NameSubject NameMarks
SamMaths100
SamLife Science70
SamPhysical Science80
JohnMaths75
JohnLife Science100
JohnPhysical Science85
TomMaths80
TomLife Science100
TomPhysical Science85

2) The output from Sorter transformation is sent into the aggregator transformation and Group by Student name is selected . As we need the final output grouped with student name.
And also in the aggregator transformation three columns are created like
1) Maths --- with formula -- Max(Marks, Subject = 'Maths')
2) Life Science --- with formula -- Max(Marks, Subject = 'Life Science')
3) Physical Science --- with formula -- Max(Marks, Subject = 'Physical Science')

3) Now Student name and Maths , Life Science , Physical Science are connected to the target.
The output obtained is as follows :

Student NameMathsLife SciencePhysical Science
Sam1007080
John7510085
Tom8010085


5 comments:

  1. good questions and answers

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. How can we do this:
    StudentName Subject1 Marks1 Subject2 Marks2
    Sam Maths 100 English 80
    John Maths 85 English 75

    to the target:

    StudentName Subject Marks
    Sam Maths 100
    Sam English 80
    John Maths 85
    John English 75

    I want to do this from flat file source

    ReplyDelete
    Replies
    1. Hi Arka,
      Sorry for the late reply
      Please use normalizer transformation inbetween source qualifier t/f and target.
      give the occurance of subject and marks as "2".
      Please view normalizer t/f post for same scenario
      Regards,
      Sri

      Delete
  4. can we join non relation table with out any joiner t/r

    ReplyDelete

Please Give Your Comments!!

Note: Only a member of this blog may post a comment.