IICS(Informatica’s Intelligent Cloud Services) : Mostly used expression logics for ETL.

Dear, reader welcome to my blog on IICS. I have recently started working on IICS Informatica’s Intelligent Cloud Services and I have faced several issues for which there was very minimal support found on internet.

So, sharing some important learnings which I got while working on it:

1. Convert string to Date Format with specified format : we use to_date function to convert any column to Date Format.

# to_date will help to convert to date format
# SUBSTR is used to select string for any specified range 0,10 denotes it will consider characters from 0 to 10th position
# 'YYYY-MM-DD' is the format in which you want your data to be in.

to_date(SUBSTR(COLUMN_NAME, 0, 10),'YYYY-MM-DD')

to_date(COLUMN_NAME,'YYYY-MM-DD')

2. Trim Left and Right spaces : Trim plays are crucial role when you are dealing with VARCHAR/STRING data and you want to avoid any duplications into your data.

#LTRIM deletes the Left Trailing Space
#RTRIM deletes the Right Trailing Space

LTRIM(RTRIM(COLUMN_NAME))

3. Convert your String to upper/lower case: Changing case logic will convert all the characters of your data into upper/lower case and it is sometimes very important to apply in your data to standardise it when you see inconsistency in your data and to avoid any duplication.

# UPPER will convert to upper case
# LOWER will convert to lower case

UPPER(COLUMN_NAME)
LOWER(COLUMN_NAME)

4. Fill NULLs with any constant value : Whenever you are dealing with data it is a standard DWH (Data Ware Housing) guideline to not have any blanks and we fill NULLs with standard value.

#When your datatype is to NUMBER/INTERGER/DECIMAL type
#This will check for all the values of your column and it any NULL is found it will fill it will 0.

IIF(ISNULL(COLUMN_NAME),0,COLUMN_NAME) 

# When your datatype is to STRING/CHAR/VARCHAR type
# In this case we will need to use quotes.
IIF(ISNULL(COLUMN_NAME),'0',COLUMN_NAME)
IIF(ISNULL(COLUMN_NAME),'NOT_AVAILABLE',COLUMN_NAME)

# In some cases ISNULL does not work so we can also use this
IIF(ISNULL(COLUMN_NAME) OR COLUMN_NAME = '','0',COLUMN_NAME)

5. Concatenate multiple columns : When there is any case where you need to combine 2 or more columns to derive any column we use concat or ||.

# To combine multiple columns we use this

COLUMN1||COLUMN2||COLUMN3||COLUMN4

6. Perform any mathematical operation on any column: Sometimes we need to derive any column by adding, subtracting, multiplying, dividing etc.

# Suppose we need our data to be multiplied by 2 we use this

COLUMN_NAME * 2

# Similarly we can perform any operation

7. Convert data to any decimal precision value : Suppose your data contains amount data any you need your data to contain data till specific decimal point. This is the expression you can use for this case:

# the function TO_DECIMAL(AMOUNT, 4) is used to convert the value to a decimal number with 4 decimal places.

TO_DECIMAL(AMOUNT,4)

8. Using system variables: This is useful when you need to capture the current date and time, task name, mapping name etc for various purposes such as logging or time-stamping records.

# To fetch the current task name use
'$CurrentTaskName'

# To fetch the current mapping name use
'$CurrentMappingName'

# To fetch RunID use
'$CurrentRunID'

# To fetch the current system date and time when a task or mapping is executed.
# This is useful when you need to capture the current date and time for various purposes such as logging or timestamping records.
'$SYSDATE'

# To fetch the date and time when the current session (task or mapping run) started.
# This is particularly useful when you need to measure the duration or elapsed time of a session, or when you want to calculate relative time differences within a session.
'$SESSSTARTTIME'

Thank you!

If this post helped you, follow me for more content and consider subscribing to my profile on Medium. Thank you!