how to create a new record dynamically using informatica powercenter

Question

I have employee's leaves related data and payment related information. e.g. Employee E1 has taken maternity leave this year. She needs to paid for 6 months and if she is on leave for greater duration (like 8 months) , I need to create two records for her.

One for the allowed duration and the other for extended duration.

Employee  LeaveStartDAte  LeaveEndDate Total_days_taken  Total_days_allowed LeaveType
e1         1Jan2013       31Aug2013     242                 186                 ML

Target expected :

Employee  LeaveStartDAte  LeaveEndDate Leavetype
e1        1Jan2013        30June2013     ML
e1        1July 2013      31Aug2013      Extended ML

How can create the second record dynamically in informatica mapping?

Answer 1

Generally speaking, we use java transformation in informatica to dynamically create new rows. However, for scenarios like the one you described, where you only need to create one extra row based on some condition you can achieve this by adding two target instances and populating the second target instance conditionally (using a router or filter transformation).

You can do something like this:

Create two sets of ports for LeaveStartDate, LeaveEndDate and LeaveType in an expression, and calculate their values accordingly. For example:

LeaveStartDate1 -> source LeaveStartDate
LeaveStartDate2 -> LeaveStartDate + Total_days_allowed + 1

Now connect first set of ports directly to a target instance. Connect the second set of ports to another target instance through a filter. The filter condition would be something like Total_days_taken > Total_days_allowed. You can also do this using a router, if you like.