Informatica Java Transformation

Suppose we have the requirement where our source data looks like this:

Source Data:

Here we have a service start date and service end date tied to a customer.

Now we want my target table data in a flattened manner like this:

Target Data:

i.e. we want to split the service start date and service end dates on a yearly basis.

The first thing that comes to mind with this situation is to use Informatica Normalizer. Thats TRUE. But if you think twice, you will find that we need to assume or hard-code one thing. That means you should consider that either the time span should have a fixed maximum value. Actually say the maximum span between the start and end date should be 5 years.

Knowingly you are trying to set the number of occurences of the Normalizer. Next you will be using a expression transformation followed by a filter to achieve the requirement. But in this manner the requirement would not be satisfied when a customer having tenure more than 5 years.

Now here we will be using a small portion of Java Code. The real raw power of Java programming language called from Informatica Powercenter will do the data transformation. Lets go straight to the mapping and the code.

Find the Java Code:-

try
{
    DateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
    Calendar cal1 = Calendar.getInstance();
    Calendar cal2 = Calendar.getInstance();
    
    int st_yr, ed_yr, st_mon, ed_mon, st_date, ed_date, st_ldm, ed_ldm;
    String str;
    Date st_dt = (Date)formatter.parse(SVC_ST_DT);
    Date ed_dt = (Date)formatter.parse(SVC_END_DT);

    cal1.clear();
    cal1.setTime(st_dt);
    cal2.clear();
    cal2.setTime(ed_dt);

    st_yr = cal1.get(Calendar.YEAR);
    ed_yr = cal2.get(Calendar.YEAR);
        
    do
    {
        OUT_NAME = NAME;
        OUT_CUST_ID = CUST_ID;
        
        OUT_SVC_ST_DT = formatter.format(st_dt);
        if(ed_yr != st_yr)
        {
            str = "31/12/" + st_yr;
            st_dt = (Date)formatter.parse(str);
            cal1.setTime(st_dt);
            OUT_SVC_END_DT = formatter.format(st_dt);
        }
        else
        OUT_SVC_END_DT = formatter.format(ed_dt);
        generateRow();
        
        st_yr = st_yr + 1;
        str = "01/01/" + st_yr;
        st_dt = (Date)formatter.parse(str);
        cal1.setTime(st_dt);
        st_yr = cal1.get(Calendar.YEAR);

    }while(ed_yr >= st_yr);

}
catch (ParseException e)
{
    System.out.println(e);
}

Next now if we want to transform and load the data on a monthly basis.

Simply find the Mapping and the Code.

Find the Java Code:-

try
{
    DateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
    DateFormat formatter1 = new SimpleDateFormat("dd/M/yyyy");
    Calendar cal1 = Calendar.getInstance();
    Calendar cal2 = Calendar.getInstance();
    
    int yr, st_mon, ed_mon, st_ldm;
    String str;
    Date st_dt = (Date)formatter.parse(SVC_ST_DT);
    Date ed_dt = (Date)formatter.parse(SVC_END_DT);

    cal1.clear();
    cal1.setTime(st_dt);
    cal2.clear();
    cal2.setTime(ed_dt);

    yr = cal1.get(Calendar.YEAR);
    st_mon = cal1.get(Calendar.MONTH)+1;
    ed_mon = cal2.get(Calendar.MONTH)+1; 
    st_ldm = cal1.getActualMaximum(Calendar.DAY_OF_MONTH);
        
    while(ed_mon != st_mon)
    {
        OUT_NAME = NAME;
        OUT_CUST_ID = CUST_ID;
        OUT_SVC_ST_DT = formatter.format(st_dt);

        if(ed_mon != st_mon)
        {
            str = st_ldm + "/" + st_mon +"/" + yr;
            st_dt = (Date)formatter1.parse(str);
            cal1.clear();
            cal1.setTime(st_dt);
            OUT_SVC_END_DT = formatter.format(st_dt);
        }
        else
        {
            OUT_SVC_ST_DT = formatter.format(ed_dt);
        }
        generateRow();
        
        st_mon = st_mon + 1;
        str = "01/" + st_mon + "/" + yr;
        st_dt = (Date)formatter1.parse(str);
        cal1.clear();
        cal1.setTime(st_dt);
        st_mon = cal1.get(Calendar.MONTH)+1;
        st_ldm = cal1.getActualMaximum(Calendar.DAY_OF_MONTH);
    }

    OUT_NAME = NAME;
    OUT_CUST_ID = CUST_ID;
    OUT_SVC_ST_DT = formatter.format(st_dt);
    OUT_SVC_END_DT = formatter.format(ed_dt);
    generateRow();
}
catch (ParseException e)
{
    System.out.println(e);
}

Note: You can extend PowerCenter functionality with the Java transformation which provides a simple native programming interface to define transformation functionality with the Java programming language. You can use the Java transformation to quickly define simple or moderately complex transformation functionality without advanced knowledge of the Java programming language.

For example, you can define transformation logic to loop through input rows and generate multiple output rows based on a specific condition. You can also use expressions, user-defined functions, unconnected transformations, and mapping variables in the Java code.