How to Handle Nulls in IDMC/IICS Mappings: A Real-World Scenario

How to Handle Nulls in IDMC/IICS Mappings: A Real-World Scenario is the Challenging Question that we will face in an interview

Business Goal: The goal is to load clean customer records into one target and send null-containing records to another for rejection tracking

Source Customer DDL:

Create Table Customer(
CustomerID INT NOT NULL PRIMARY KEY,
CustomerName VARCHAR(100)   NOT NULL,
Salary DECIMAL(10,2)  NOT NULL,
DateOfBirth DATE NOT NULL,
City VARCHAR(50)    NOT NULL);

NOTE: We would Require to load cleansed records into Customer_Clean and Rejected tracking records into Customer_Reject_Log

Target Customer DDL’s: 

CREATE TABLE Customer_Clean
(CustomerID INT NOT NULL PRIMARY KEY,
CustomerName  VARCHAR(100) NOT NULL,
Salary DECIMAL(10,2)  NOT NULL,
DateOfBirth DATE NOT NULL,
City VARCHAR(50)    NOT NULL);

CREATE TABLE Customer_Reject_Log
(CustomerID INT NULL,
CustomerName   VARCHAR(100)   NULL,
Salary  DECIMAL(10,2)  NULL,
DateOfBirth    DATE NULL,
City VARCHAR(50)    NULL,
RejectReason  VARCHAR(500) NOT NULL,
Loadtime DATE NOT NULL);

How to Handle Nulls in IDMC/IICS Mappings: A Real-World Scenario

Lets Start Designing IDMC Mapping to achieve Null Handling

Step 1: Select the Source Table(Customer) created from Database in Mapping Design

Step 2: Create Expression Transformation to the downstream next to Source Tr to Handle Null values, rejection tracking message

Step 2.1: Pull all Incoming fields which come from Source

Step 2.2: We will create output ports for Customername, Salary, City, DataofBirth to make as cleansed records (make as defaults for null values which come from source) as per Bussiness Requirments which will go Customer_Clean Table

Stepe2.2.1: Below Screenshot i have created output port with ‘o_customerName’ i am making sure for any null values come from source passing defaults value as ‘UNKNOWN’ for cleansing

Stepe 2.2.2: Repeat Steps for Salary, City, DataofBirth ports and Make Default values

Step 2.3: We will create output port to track ‘Reject Reason’ as per Bussiness Requirments which will go Customer_rejected_log Table

Stepe 2.3.1: Below Screenshot i have created output port with ‘o_RejectReason’, This logic is used to generate a descriptive error message for rejected records in an IICS mapping. It checks each column for NULL values and builds a string listing which fields were missing

Step 2.4: We will create another output port to track flag records that should be rejected based on null check

Step 2.4.1: Below Screenshot i have created output port with ‘o_IsRejected’, It checks if any of the listed columns (CustomerName, Salary, DateOfBirth, City) is NULL. If any column is null, it returns 1 (meaning the record is rejected). If none are null, it returns 0 (meaning the record is clean).

Final View of Expression Transformation

Step 3: Create Router Transformation next to the downstream Expression Tr to Handle flag records that should be rejected based on null check

Step 3.1: Include below Ports which come from Expression Tr in Router.

1. Notice here we are not touching ‘CUSTOMERID‘ where we will not get any NULL values from source, as this is Primary Key
2. Keep Remaining Ports which we created as output in Expression

Step 3.2: Exclude below Ports which come from Expression Tr in Router where those are original fields which do not required where we already handling null checks by creating output ports.

Step 3.3: Create Two Output groups in Router Tr based on the o_IsRejected flag created in the Expression step

Clean_Group (Condition: o_IsRejected = ‘0’)
This group routes all records that passed validation (no nulls in mandatory fields) to the clean target table.

Reject_Group (Condition: o_IsRejected = ‘1’)
This group routes all records that failed validation (one or more mandatory fields were null) to the reject log table.

Step 4.1 : Create Expression Transformation next to the Router Tr to avoid fields conflicts before it load to table CUSTOMER_CLEAN
or

Step 4.1 : You can directly Rename the incoming fields in Target Transformation

Step 4.1.1 : Exclude below Ports which come from Router Tr where this ports not required to have in CUSTOMER_CLEAN, meaning this table will only have cleansed records after we handle null values from source data

Step 4.1.2 : Rename below Ports to avoid fields conflicts that should match with Target Fields

Reason: The o_CITY, o_CustomerName, o_DATEOFBIRTH and o_SALARYports where we already handled Null values and passed default values to this ports, and only this data should go to Target Table

Final View of Expression Transformation

Target CUSTOMER_CLEAN

Target Incoming Fields: Attaching below screenshot for Target CUSTOMER_CLEAN

Target: Select the CUSTOMER_CLEAN which we created in Database

Field Mapping: Make Sure do the Field Mapping by clicking on Smart Map refer for below screenshot

After doing the Field Mapping

Lets Repeat same steps for Other Target which is Customer_Reject_Log

Step 4.2 : Create Expression Transformation next to the Router Tr to avoid fields conflicts before it load to table Customer_Reject_Log
or

Step 4.2 : You can directly Rename the incoming fields in Target Transformation

Step 4.2.1 : Exclude below Port which come from Router Tr where this port will only hold logic to route for both gorups, meaning this port is not required as per Target DDL structure and per BRB requirements

Step 4.2.2 : Rename below Ports to avoid fields conflicts that should match with Target Fields

Step 4.2.3 : Create output port, to hold loadtime for Target Table

Target Customer_Reject_Log

Target Incoming Fields: Attaching below screenshot for Target Customer_Reject_Log

Target: Select the Customer_Reject_Log which we created in Database

Field Mapping: Make Sure do the Field Mapping by clicking on Smart Map refer for below screenshot

After doing the Field Mapping

Step 5(optional): You can create Mapping Task and Test your Mapping, since this mapping does not have any parametrization concepts, I am going to run Mapping Directly

Click on RUN Option on Top Right Corner

Select Your Runtime Environment by choosing drop down and the click on RUN

Results 🙂

Target CUSTOMER_CLEAN Results

This We expected as we do not have any null values for all the fields coming from source for CustomerId=101

Target Customer_Reject_Log

This output shows the rejected records log after applying null-handling and validation rules. Each row contains the original data along with a RejectReason column that explains why the record was rejected.

For example:
If CustomerName was missing, the reason says: “CustomerName is NULL”.
If multiple fields were null, the reason lists all of them, like: “Salary NULL; DateOfBirth NULL; City is NULL”.

Conclusion 🙂

I Hope This Blog ‘How to Handle Nulls in IDMC/IICS Mappings: A Real-World Scenario’ can help understanding how to trace Null records and how to log them for different Purpose , For any Questions feel free to shoot a message in Comment box or reach out here Contact

IICS Lookup Condition Why Your Lookup returns NULL or Multiple Rows