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

       

      Source

       

       

      COL_ACOL_BCOL_CCOL_D
      ABC1234678A0
      CDE1234891B0
      CDE1234198C0
      ABC2345201B1
      XYZ2345301C1

       

       

      Required Target

       

       

      COL_ACOL_BCOL_CCOL_DCOL_ECOL_FCOL_GCOL_H
      ABC1234678A0MasterABC678A0
      CDE1234891B0ChildABCA1
      CDE1234198C0ChildABCA2
      ABC2345201B1MasterABC201B1
      XYZ2345301C1ChildABCB2
        • 1. Re: Fetching values from group of rows
          JanLeendert Wijkhuijs Guru

          Hi,

           

          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.

           

          Regards,

          JanLeendert

          • 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.
            Note:

            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,

            Syed

            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

              Regards,

              JanLeendert

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

                Hi,

                 

                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

                 

                COL_A

                COL_B

                COL_C

                COL_D

                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.

                 

                 

                Regards,

                JanLeendert

                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.

                    Regards,
                    JanLeendert