Convert Columns to Dynamic Rows in Informatica Using Java Transformation - Perficient Blogs
Blog
  • Topics
  • Industries
  • Partners

Explore

Topics

Industries

Partners

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.

Informatica Normalizer

 

 

 

 

 

 

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

Informatica Normalizer

 

 

 

 

 

 

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:Informatica Normalizer
    2. Target properties:Informatica Normalizer 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.
    • Informatica Normalizer
  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.
    • Informatica Normalizer
  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.
    • Informatica Normalizer
  8. Drag the java output ports and connect to the target. Validate the mapping and save.
    • Informatica Normalizer
  9. Create a workflow for the above mapping and execute the job. The output result is shown below,Informatica NormalizerHope this helps! I’ll follow up shortly with another interesting blog..!

One thought on “Convert Columns to Dynamic Rows in Informatica Using Java Transformation

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe to the Weekly Blog Digest:

Sign Up