LEAD and LAG implementation in Informatica Cloud (IICS)

Chapter-4

Introduction

If you have experience working with SQL, you must be already familiar with LEAD and LAG Analytic functions.

LAG function helps to fetch the row details of the previous record in the current record. LEAD function fetches the row details of next record in the current record.

Let us discuss with examples how to implement the Lead and Lag scenarios in Informatica Cloud.

Consider the following as the Source data

Q1. Design a mapping to read previous year’s sale amount in the current record

Expected Output:

Solution

1. Select the object with above data as source and pass the data to an Expression transformation. (Assuming data is sorted)
2. In the expression transformation, create a variable port V_Count and assign expression as V_Count+1 to increment the value by 1.
3. Create another variable V_Previous_Sale_Amount and assign the expression as IIF(V_Count=1, NULL,V_Sales_Amount).
4. Create another variable V_Sale_amount and assign expression as Sale_Amount.
5. Finally create an output port Previous_Sale_Amount and assign value as V_Previous_Sale_Amount.
6. The fields created in the expression transformation will be as below
   V_Count = V_Count+1             
   V_Previous_Sale_Amount = IIF(V_Count=1, NULL, V_Sales_Amount) 
   V_Sales_Amount = Sale_Amount                
   Previous_Sale_Amount = V_Previous_Sale_Amount
7. Map the data from expression transformation to target.

We are identifying the first record using the sequence number and assigning the value as NULL as there will be no previous record for that. By storing the Sale_Amount using a variable in current record, we are able to use it in the next record as a Previous_Sale_Amount value.

Q2. Design a mapping to read find the difference in amount of Sales between current and previous year

Expected Output:

Solution

The entire process is same as explained in the above example. Add the extra output field Difference and assign the expression value as Sale_Amount-V_Previous_Sale_Amount

The fields in the expression will be as below

V_Count = V_Count+1    
V_Previous_Sale_Amount = IIF(V_Count=1, NULL, V_Sales_Amount) 
V_Sales_Amount = Sale_Amount       
Previous_Sale_Amount = V_Previous_Sale_Amount
Difference = Sale_Amount – V_Previous_Sale_Amount

Q3. Design a mapping to read next year’s sale amount in the current record

Expected Output:

Solution

1. Select the object with above data as source and pass the data to two Expression transformations. (Assuming data is sorted)
2. In one expression transformation, create a variable and output fields as below to assign sequence numbers to the input records.
   V_Count = V_Count+1    
   O_Count = V_Count
   
   The sequence starts with 1 and increments by 1 for each record.
   
3. In one expression transformation, create a variable and output fields as below to assign sequence numbers to the input records.
   V_Count = V_Count+1    
   O_Count = V_Count-1
   
   The sequence starts with 0 and increments by 1 for each record.
   
4. Map the data from both the expression transformations to a joiner transformation. Expression-1 as Detail and Expression-2 as Master.
   Rename the fields from expression-2 by adding the prefix as “Next_” to resolve the Field Name Conflicts.
   Select the Join type as Master Outer and join condition as below
   Next_O_Count = O_Count
   Make sure to enable the Sorted Input option in the Advanced tab.
5. Map the data from joiner transformation to target. In the target Incoming Fields section, select only the required 3 output fields.

By taking two different flows of source data and assigning the sequence numbers for one starting with 1 and other starting with 0, we are able to join the next record with current record.

Q4. Design a mapping to find the difference in amount of Sales between next year and current year

Expected Output:

Solution

The entire process is same as explained in the above example.
Add an expression transformation after joiner transformation and create an output field Difference and assign value as Next_Sale_Amount-Sale_Amount

Difference = Next_Sale_Amount-Sale_Amount

Map the data from expression transformation to the target. In the target Incoming Fields section, select only the required 4 output fields.

Q5. Design a mapping to read previous and next year’s sale amount in the current record

Expected Output:

Solution:

The entire process is a combination of examples Q1 and Q3 we discussed above.

1. Map source data into two expression transformations.
2. In one expression transformation, create fields as shown below
   V_Count = V_Count+1    
   V_Previous_Sale_Amount = IIF(V_Count=1, NULL, V_Sales_Amount) 
   V_Sales_Amount = Sale_Amount       
   Previous_Sale_Amount = V_Previous_Sale_Amount
   O_Count = V_Count
3. In another expression transformation, create fields as shown below
   V_Count = V_Count+1    
   O_Count = V_Count-1
4. Join data from both the expression transformations using a Joiner transformation with expression-1 as detail and expression-2 as master. Use join type as Master outer and join condition as Next_O_Count = O_Count.
5. Map the data from Joiner to and Expression transformation and create 3 output fields as below
   Previous_Amount = Previous_Sale_Amount
   Current_Amount = Sale_Amount
   Next_Amount = Next_Sale_Amount
   
6. Map the data from expression transformation to the target. In the target Incoming Fields section, select only the required 4 output fields.

Lead and Lag Functions in Informatica Cloud

Informatica Cloud supports using LEAD and LAG function in expression transformation in Elastic Mappings. LEAD and LAG functions are not supported in regular cloud mappings.

Your organization must have licence to create Elastic mappings.

Syntax to create LEAD function

LEAD ( column_name, offset, default )

Syntax to create LAG  function

LAG ( column_name, offset, default )

The following table describes the arguments for this command:

So the field in expression transformation of our LAG and LEAD mappings examples will be as below

Previous_Sale_Amount = LAG(Sale_Amount, 1, NULL)
Next_Sale_Amount = LEAD(Sale_Amount, 1, NULL)

Conclusion

Implementing LAG is simple and pretty straight forward.

Implementing LEAD is not as simple as compared to LAG as you need to take two different flows of source data and assign different sequence numbers to make a join to fetch the Next sales amount value.

Implement LEAD and LAG when you need to access the next or previous record in the current record. It helps in comparing the data in both ways with past and future data sets.