I am creating an informatica workflow which can extract data from two tables of DB2 database and load into a temporary table.
Suppose the two source tables name are
Account (Parent) and
They have 1:M relationship. Means an Account can have many Activities (Account.PK = Activities.FK).
Activities table has two columns- first 'Type' whose value could be
'Not-Paid'.And second column is
'Created_Date' datetime datatype, whenever you create new activity record, date and time would get stamp in this field.
Now, condition to load data in temporary table is - "
For an Account record, it would 1st check in Activities table for today's Paid activities (Type = Paid). If it finds more than one paid activities, then it would pick the Latest created one (Created_Date column) out of them. If there is no Paid activity record for the Account, then it would pick latest created 'Will-Pay' activity."
Means, it should pick latest Paid activity for today (Sysdate) for an Account, if it is not present then only It will pick latest Will-pay activity for today.
Please help me to understand how I can implement this logic in Informatica workflow and which transformations I should use and how?
Thanks alot. Kindly help.