1 Reply Latest reply on Feb 20, 2018 1:25 AM by Nico Heinze

    Count within a group

    King Singh New Member

      Hi,

       

      I want to calculate count of records for each group without using Aggregator Transformation and without using SQ override. Think we can achieve this using variable ports but I do not know how to do it.

      Can someone please suggest the correct mapping and logic to be used?

       

       

       

      Thanks,

      King

        • 1. Re: Count within a group
          Nico Heinze Guru

          I only know how to do this in PowerCenter, not in PowerCenter Express (which are, despite the similar names, completely different products, not to forget that PowerCenter Express has been decommissioned by Informatica some time ago).

           

          In PowerCenter, you can use an Expression transformation and some variable ports to hold the "primary key" values from the previous input record; you can simply compare them with the "primary key" values from the current input record and, if they differ, forward all port values from the previous record (also kept in variable ports) to some output ports.

          Furthermore you should set one additional output port to TRUE if these "primary key" ports differ and to FALSE if they are the same.

          Forward the data including this additional port to a Filter transformation.

          In this Filter transformation, forward the data only if this additional output port from the EXP was set to 1 (meaning the Filter will forward the last record of the previous "primary key" values).

           

          Now this has one disadvantage: you will not get the last record this way.

          The easiest way to remedy this problem is to make sure that at the end of the input data stream there's one additional record which contains some "primary key" values which do not exist in real life. As long as this additional "dummy" record is fed into the EXP and the FIL and processed as mentioned above, the Filter will then forward the last "real" input record (because the flag output port from the EXP will be set to 1 for the current record).

           

          In short: that can be done but it's pretty tricky and can lead to some interesting additional challenges. Usually not worth the effort.

           

          Regards,

          Nico