Convert Columns to Dynamic Rows in Informatica Using Java Transformation

Most of us know that the Informatica Normalizer transformation is being used to convert columns into multiple rows. But one of the limitations is that the occur clause in the Normalizer transformation is fixed for all the input rows from the source.

For example, if the requirement is to generate n number of rows for each input record from the  source (where ‘n’ is varying for each input record), then the occur clause in the Normalizer transformation cannot be set/defined dynamically to achieve the goal.

Consider the following input and the expected output result: the below incoming source file has 3 fields – PolicyNum, PolicyHolder and ClaimDetail. The ClaimDetail column has a list of values about the claim number and its claim amount of that policy.

The Future of Big Data

With some guidance, you can craft a data platform that is right for your organization’s needs and gets the most return from your data capital.

Get the Guide

A policy record should be generated based on the number of claims listed in the claim detail column as the output result.

By writing a simple java code using Informatica Java transformation, the above scenario can be achieved.

Let’s get started to create a map for this scenario,

1. In mapping designer, create a mapping and pull the source and target definition into the work area. Here, source and target are comma separated files.
   1. Source properties:
   2. Target properties:
2. Click Transformation-> Create. Select Java Transformation.
3. Select the option “Active” when it prompts you for Java transformation type and click Done.
   • 
4. Drag in the input ports from source qualifier to Java transformation.
5. Open the Java transformation and create 4 output ports – Policy_number, Policy_holder, claim_number and claim_amount.
   • 
6. Move on to “Java code” and click on “ On Input Row” tab and include the below java script
   • String [] column_split;/* declare string array to store the input claimdetail data */ String column_delimiter = “\\|”;

     /* declare string variable to store the delimiter (‘|’) which separates the claim record. Basically, this defines the occur clause. Since ‘|’ is a special character in java regular expression to match any single character, either double backslash ‘\\’ is used before pipe symbol or the pipe can be enclosed within square brackets [|]. In Java, 1) The characters .^$|*+?()[{\ have special meaning outside of character classes. 2) The characters ] ^-\ have special meaning inside of character classes.*/

      

     String [] column_data;

     /* declare string array to store claim number & claim amount */

      

     String data_delimiter = “=” ;

     /* declare string variable to store the delimiter between claim number & claim amount */   

                                                                            

     Column_split = CLAIM.DETAILS.split(column_delimiter);

     /* Using Split function, each claim data is extracted based on ‘|’ delimiter and it has been stored in an array*/

      

     o_POLICY_NUMBER = POLICY_NUMBER ;

     o_POLICY_HOLDER = POLICY_HOLDER ;

     /* Mapping source input ports to java output ports*/

      

     for (int I =0;  i < column_split.length; i++)

     /*Loop begins to generate claim record*/

     {

     column_data = column_split[i].split(data_delimiter);

     /* Using Split function, claim number & claim amount is extracted based on ‘=’ delimiter and it has been stored in an array */

     o_CLAIM_NUMBER = column_data[0];

     o_CLAIM_AMOUNT = Double.parseDouble(column_data[1]);

     /* parser to convert amount value from string to double*/

     generateRow();

     }

      

7. After including the java code , click on compile to verify that the written code does not throw any errors. If it succeeds, then you can see the statement “ Java Code compilation successful” at the bottom. Apply the changes and click OK.
   • 
8. Drag the java output ports and connect to the target. Validate the mapping and save.
   • 
9. Create a workflow for the above mapping and execute the job. The output result is shown below,Hope this helps! I’ll follow up shortly with another interesting blog..!