Loops in Informatica

The simplest way to implement Loops in Informatica is explained below.

Source Name: Product_Details

Sample Input:

Pcode	Pname	Pdetails
Water12	Himalaya Water	Price,1.20;Qty,250ml;Brand,Himalaya;Class,Liquid
Snack23	Mad Pringles	Price,0.65;Qty,165 g;Brand,Mad Ltd;Class,Snacks;Batch,12312334;Exp,12/Feb/2012

note: in the pdetails column each product attribute separated with semi colon “;”

Targets: Product_Dim, Product_Details

Expected Output:

Product_Dim

Pskey	Pcode	Pname
P1	Water12	Himalaya Water
P2	Snack23	Mad Pringles

Product_Details

Pskey	Sno	Type	Value
P1	1	Price	1.20
P1	2	Qty	250ml
P1	3	Brand	Himalaya
P1	4	Class	Liquid
P2	1	Price	0.65
P2	2	Qty	165 g
P2	3	Brand	Mad Ltd
P2	4	Class	Snacks
P2	5	Batch	12312334
P2	6	Exp	12/feb/2012

If you closely observe the above Source and Target sample data, we need to have some kind of loop logic to extract the “Product_Details” from Pdetails column. Assume that each product has a different set of attribute values which can’t be predicted I.e. the number of attributes for each product varies.

                                                                                                                                                    

Informatica Mapping:

Step1: Create a new table or a Flat file with the below structure and values

Tablename : Loop_Tab No_Loops Loop_Seq 1 1 2 1 2 2 3 1 3 2 3 3 4 1 4 2 4 3 4 4 . . . . etc etc

You can create this table using a simple PL/SQL script or VB script, in my example I created a table using below script. CREATE TABLE LOOP_TAB (  NO_LOOPS NUMBER(3), LOOP_SEQ NUMBER(3) ); BEGIN FOR I IN 1..100 LOOP             FOR J IN 1 .. I LOOP                                                  INSERT INTO LOOP_TAB VALUES (I,J);             END LOOP; END LOOP; COMMIT; END;

Step2: Create a mapping as shown in below picture

1. Pull the “Product_Details, LOOP_TAB” sources to mapping designer.
2. Create a “Sequence Generator”   transformation as show in picture.
3. Create a Expression Transformation and pull all ports from SQ_ Product_Details and also pull the NEXTVAL from SEQTRANS.

Now add a new output port in Expression and name it as “out_NO_LOOPS” also change the data type to Integer, Now write the below expression in that output port “LENGTH(PDETAILS)-LENGTH(REPLACECHR(1,PDETAILS,';','')) “.

Basically from the above expression we will get to know how many attributes exists from a product.

4. Now take a Joiner Transformation and Pull “LOOP_TAB” ports as Master Table and All the ports from “Exp_Find_No_loops” as Detail ports and apply Normal Join on NO_LOOPS= out_NO_LOOPS .
5. Now Pull the targets “PRODUCT_DIM, PRODUCT_DETAILS” to mapping designer and connect them as per above mapping picture.
6. You are done , All the best … Srikanth Mullamuri