6 Replies Latest reply on Aug 6, 2021 12:38 AM by JanLeendert Wijkhuijs

    Fetching values from group of rows

    Kamal Haria Active Member

      Hi All


      I have a table with following format with Col B as key. For group of values in Col B, if the col A has code as 'ABC', then that record has to be treated as Master record and rest of the records of that key should be child key additional columns to be created as target. The below are tables for clear understanding


      The columns in target table will have following data values


      Col_E: If value of col A is 'ABC' for set of same values of Col B , then record with col_A as ABC should be tagged as 'Master' and rest of the records of same key as 'Child'


      Col_F: Will populate the master record values i.e. ABC for all the records of the same values of col B


      Col G: Populate same value of col_C just for master record. Rest child records should be null


      Col H: For Master record, populate same value as COL_D (Alpha numeric with last digit is always a number) but for child record, increment the value by 1, as shown in the table








      Required Target



        • 1. Re: Fetching values from group of rows
          JanLeendert Wijkhuijs Guru



          I'm a bit puzzled

          It looks like your example for a few records doesn't match the description or am I mistaken?

          ABC    2345      201      B1

          XYZ     6789      301     C1


          ABC      2345      201      A1      Master      ABC      201      B1

          XYZ      6789      301      B1      Child         ABC                  B2


          These to appear not to be belonging to the same group but "COL_H" value is giving the impression.




          • 2. Re: Fetching values from group of rows
            Syed Aziz Guru

            Hello Kamal,


            To Fetch the SECOND, THIRD record in a group similar to FIRST() and LAST() built-in function in Informatica, use a combination of transformations Rank and aggregator and perform the following:

            1. In the Rank transformation,
              1. Select the group by ports on which you need to perform the grouping (For example Col1).
              2. Choose the Rank port as the column2 for which you need the second value.
              3. Set the Number of ranks to 2, if you want to have second value.
                Result of data from the Rank transformation with Top 2 rows from each group based on the group by ports.
            2. Pass this data to the aggregator, and use the LAST() function. This is going to return the second value as you have top two values for each group.

            If you need to fetch the third value, then set the Number of ranks to 3 and aggregator Last() function will fetch the third row. The following is a snapshot of the Mapping:

            Best regards,


            1 of 2 people found this helpful
            • 3. Re: Fetching values from group of rows
              JanLeendert Wijkhuijs Guru

              Hi Syed,

              Your answer isn't complete, you forgot all about COL_E, COL_F, COL_G and COL_H



              • 4. Re: Fetching values from group of rows
                JanLeendert Wijkhuijs Guru



                Based on the example where COL_B = 1234 this is a pretty straight forward problem.


                I would go for source / source qualifier in a mapping and after the SQ an expression.

                In this expression I would add a variable port (var_master_child) with datatype integer and an output port (out_master_child) also datatype integer.

                Logic in the variable port: IIF(COL_A = 'ABC', 1, 2)

                out_master_child var_master_child


                Next I would have a sorter sorting on COL_B, out_master_child (and probably COL_D ??)


                Then another expression to create all the output needed.


                Ports would be






                out_master_child (only input port)

                var_COL_H_char      IIF(COL_B <> var_prev_COL_B,SUBSTR(COL_D,1,1),var_COL_H_char)

                var_COL_H_num      IIF(COL_B <> var_prev_COL_B,TO_INTEGER(SUBSTR(COL_D,2,1)),var_COL_H_num) integer variable

                var_COL_H_addition IIF(COL_B <> var_prev_COL_B,0,var_prev_COL_B + 1)    integer variable

                var_COL_E                IIF(out_master_child = 1,'Master','Child')

                COL_E                       var_COL_E

                COL_F                       'ABC'

                var_COL_G                IIF(out_master_child = 1,COL_C,Null)

                COL_G                        var_COL_G

                COL_H                        var_COL_H_char||TO_CHAR(var_COL_H_num + var_COL_H_addition)

                var_prev_COL_B        COL_B



                The above only works when there is always a record available for every COL_B key value where there is also a COL_A = 'ABC'

                If not then the functional description needs an addition and the mapping also needs some additional checking logic.





                1 of 1 people found this helpful
                • 5. Re: Fetching values from group of rows
                  Kamal Haria Active Member

                  Hi JanLeendert,


                  Yes you are right. I have updated the table

                  • 6. Re: Fetching values from group of rows
                    JanLeendert Wijkhuijs Guru

                    Hi Kamal,

                    I've seen it.
                    The solution I proposed in my previous post will work.

                    Please let us know if your problem is solved or whether you need additional information.