2 Replies Latest reply on Feb 9, 2017 9:19 PM by inuser455268

    Informatica issue - transformation

    New Member

      I want to know how to get latest updated record via Informatica. Suppose I have 10 records in a temporary table. 3 records for Account1, 3 for Account2 and 4 records for Account3. Now out of these 3 accounts, I need fetch only those records which has maximum date value (Latest date) and insert in another temporary table. So which transformations I could use to get this or informatica logic I should use? Please help.

        • 1. Re: Informatica issue - transformation
          Guru

          It would be good if you would have posted this question in the correct sub-forum, Real-Time Alert Manager was definitely the wrong place.

           

          The technical realisation depends on the tool you work with (PowerCenter, PowerCenter Express, IDQ...), but the general outline is this:

          You source the data sorted by account ID and the date column in question. You can have the data sorted either from the source database (in case the source data are stored in a DBMS), or you can sort them in the mapping using a Sorter transformation.

          Then you push the sorted data into an Aggregator transformation. Make sure that this Aggregator transformation groups by account ID. You can either use the LAST() function to retrieve the last record for each account ID, or you simply group by account ID without using any grouping function, then the Aggregator will always deliver the last record per input group (in your case, per account ID).

          Finally you forward the output of the Aggregator to your target table.

           

          Regards,

          Nico

          • 2. Re: Informatica issue - transformation
            New Member

            Thanks Nico...