1 Reply Latest reply on Sep 18, 2020 3:03 AM by Nico Heinze

    Create a new row from multipe record types

    Kevin Wells Seasoned Veteran

      I have a source file that has multiple Record Types.  I have a requirement to get columns from Record Types 2 & 3 and append them to Record Type 16 to create a new row.

       

      Col #1 Record Type

      01 = File Header

      02 = Group Header

      03 = Status

      16 = Transactions

      49 = Account Trailer

      98 = Group Trailer

      99 = File Trailer

       

      Source File Sample:

      01,73737373

      02,100000000,100000000,1,200901,0000,USD,2/

      03,656565656565,aaa,,,,/

      16,475,9999,,444444444,27822,TEST DATA AA

      16,475,44444,,555555555,27789,TEST DATA BB                                    

      02,100000099,100000000,1,200902,0000,USD,2/

      49,33333333,36/

      98,33333333,1,38/

      03,123456789012,aaaa,,,,/

      16,469,66666666,,999999999999,0,DDDDD     47474747474747     989898989898989 TEST DATA 2

      16,469,7777777,,888888888888,0,MMMMM  9876543210         012345678999999 TEST DATA 3

      49,555555,36/

      98,555555,1,38/

      99,813681888888,7,211/

       

      New Rows:

      16,475,9999,,444444444,27822,TEST DATA AA,100000000,200901,656565656565

      16,475,44444,,555555555,27789,TEST DATA BB,100000000,200901,656565656565

      16,469,66666666,,999999999999,0,DDDDD     47474747474747     989898989898989 TEST DATA 2 ,100000099,200902,123456789012

      16,469,7777777,,888888888888,0,MMMMM  9876543210         012345678999999 TEST DATA 3,100000099,200902,123456789012

       

      Any suggestions on how ( if even possible ) to process this.

       

      Thank you in advance.

        • 1. Re: Create a new row from multipe record types
          Nico Heinze Guru

          In PowerCenter that can easily be handled. As far as I know, you might have to disable certain "optimisations" in IDQ in order to make this work the same way.

           

          In PowerCenter, when reading the file line by line, you can use an Expression transformation to keep track of the data in record types 02 and 03 via variable ports.

          Meaning that you need to define one variable port for records type 02 and one variable port for records type 03.

          Each of these two variable ports will be set to the respective fields from source lines of the appropriate record type (and in case of all other record types, these two variable ports will simply retain their current values).

          Now whenver you encounter a record of type 16, you simply append the two variable ports to the current input line.

           

          Regards,

          Nico