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.
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
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).
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,
In Oracle we can create the above table by using below procedure.
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.
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))))
Connect the SK and o_Field port to the output and save the mapping.
The final design of the mapping will be like below,
Create a Workflow for the mapping and configure every property which should be filled.
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.