Logo DWBI.org Login / Sign Up
Sign Up
Have Login?
Login
New Account?
Recovery
Go to Login
Informatica

Informatica Java Transformation

Updated on Sep 30, 2020

Feel the Power of Java programming language to transform data in PowerCenter Informatica. Java Transformation in Informatica can be used either in Active or Passive Mode.

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

Source Data:

NAMECUST_IDSVC_ST_DTSVC_END_DT
TOM131/08/200923/03/2011
DICK201/01/200431/05/2010
HARRY328/02/200731/12/2009

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:

NAMECUST_IDSVC_ST_DTSVC_END_DT
TOM131/08/200931/12/2009
TOM101/01/201031/12/2010
TOM101/01/201123/03/2011
DICK201/01/200431/12/2004
DICK201/01/200531/12/2005
DICK201/01/200631/12/2006
DICK201/01/200731/12/2007
DICK201/01/200831/12/2008
DICK201/01/200931/12/2009
DICK201/01/201031/05/2010
HARRY328/02/200731/12/2007
HARRY301/01/200831/12/2008
HARRY301/01/200931/12/2009

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.

Using Java Transform in Informatica Mapping
Using Java Transform in Informatica Mapping
Java Transform Ports Tab
Java Transform Ports Tab
Java Transform Properties Tab
Java Transform Properties Tab
Java Transform Java Code Tab
Java Transform Java Code Tab

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.

Using Java Transform in Informatica Mapping
Using Java Transform in Informatica Mapping
Java Transform Ports Tab
Java Transform Ports Tab
Java Transform Properties Tab
Java Transform Properties Tab
Java Transform Java Code Tab
Java Transform Java Code Tab

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.