Circulo (Looping) logic in Informatica

Many of us have used the looping logic in programming languages or in others tool that we use. Looping logic is when you process each record continuously until it reaches its maximum limit. So, what’s new in looping logic? Let’s see here.

In Informatica, it is quite problematic to loop a single record and there is no dispersed transformation to do this process. Though it is difficult to perform looping, we can achieve it easily by using some predefined tables and transformations.

Below is the uncomplicated method of implementing the looping concept in Informatica.

Source file:

The scenario is to process a single record twice or thrice based on the number of delimiters.

For example, here the record 100 has 3 values separated by the delimiter ‘|’. We need to loop through the input records multiple times based on the number of values it has. The desired output for the above input should be as below,

Steps to be followed:

Create source and target file or table and import those in the mapping designer

Step 1:

In this step we need to find out the number of delimiter (Here we have ‘|’ as delimiter), so that we can get the number of fields for a single record.

Here I’ve created an expression transformation to find the delimiter count.

Count is the output port which has an expression like below.

The Count expression LENGTH(Emo_ID)-LENGTH(REPLACECHR(1,Emp_ID,’|’,’’))` will give the number of delimiter values. Now, we need the number of fields, so I added 1 with this to get number of fields (LENGTH(Emo_ID)-LENGTH(REPLACECHR(1,Emp_ID,’|’,’’))+1).

Step 2:

We need to have some predefined tables or files as I mentioned initially. Here I’m using a table in SQL Server as below,

The procedure to create the above table in SQL Server is given below,

Revolutionize Your Business With Generative AI

From product design and software development to virtual agents, content creation, and reporting, GenAI is transforming business. Our AI experts help you unlock GenAI’s full potential and drive growth.

Let’s Get Started

In Oracle we can create the above table by using below procedure.

Step 3:

Create one Joiner transformation and perform an inner join operation by matching the Count port from the expression transformation with the Loop_Num port from the predefined table (test_loop) and take the Loop_Rept port from the predefined table (test_loop), SK & Emp_ID ports from the expression transformation.

The outcome of the Joiner transformations will be like below,

It would be better, if we sort the data based on an SK port.

Step 4:

Create one expression transformation and create variable ports and an output port like below,

In this example,  v_Sep is a variable port which has an expression to split the record based on delimiter using the port Loop_Rept.

The above expression will separate or split the record based on the Loop_Rept value and delimiter’s position.

Expression of v_Sep :

DECODE(Loop_Rept, Loop_Rept=1, SUBSTR(Emp_ID,1,INSTR(Emp_ID,’|’,1,Loop_Rept)-1), SUBSTR (Emp_ID, INSTR(Emp_ID, ‘|’, 1, Loop_Rept-1)+1, IIF(INSTR(Emp_ID,’|’,1,Loop_Rept)=0, length(Emp_ID), (INSTR(Emp_ID,’|’,1,Loop_Rept))-(INSTR(Emp_ID,’|’,1,Loop_Rept-1)+1))))

Step 5:

Connect the SK and o_Field port to the output and save the mapping.

The final design of the mapping will be like below,

Step 6:

Create a Workflow for the mapping and configure every property which should be filled.

Step 7:

Run the workflow once every property has been configured.

We can get the output like below after running the workflow successfully.

Thus we have achieved the desired output for the input.

I hope, this blog helps you to implement looping logic in Informatica. Kindly let me know, if the same concept can be achieved in a better way and I will come soon with another interesting blog…!