Variable Ports in Informatica

Ratings:
(4.3)
Views:4682
Banner-Img
  • Share this blog:

Variable Ports in Informatica

A port which can store the data temporarily is known as the variable port in Informatica, which is represented as (V)

A variable port is required to "simplify the complex expression" it improves the performance of calculations

A variable ports are not visible in the "Normal view". Visible only in the "edit view".

Example

Design a mapping to calculate the gross salary

Gross salary = total sal +HRA-Tax

Total sal=sal+commision

HRA = Sal*0.14

tax = sal*0.12

Procedure

Create a source definition with the name emp

create a target definition with the name emp-gross sal

(mpno, ename, job, sal, comm, totalsal, HRA, tax, gross salary)

Create a mapping with the name m-emp-variable-port

drop the source and target definitions

create the transformation type expression

From SQ EMP copy the required ports to expression transformation double click on expression transformation select the ports tab

Portname    Datatype Precession Scale I O V   Expression
V-total sal decimal 7 2  5 IF(isnull(comm)), sal, sal+comm)
V-HRA decimal 7 2  5 sal*0.14
V-Tax decimal 7 2  5 sal*0.12
total sal decimal 7 2  Screenshot_6 V-total sal
HRA decimal 7 2  Screenshot_6 V-HRA
Tax decimal 7 2  Screenshot_6 V-tax
Gross sal decimal 7 2  Screenshot_6 V-totalsal+V.HRA.V-Tax

Click apply and click ok

Example

Design a mapping to calculate the year of join (YOJ) from hire date

Procedure

Create a source definition with the name EMP

Create a target definition with the name Emp-YOJ-N

(Empno, tname, job, hiredate, YoJ, saldeptno)

YOJ data type is number (p,s)

Create a mapping with the name m-employee-YOJ-N

Drop the source and target definitions

create the transformation type expression

from SQ-EMP copy the required ports to expression transformation double click on expression transformation select the ports tab from tool bar click and add a new port (:)

If you want to enrich your career and become a professional in Informatica, then visit Tekslate - a global online training platform: "Informatica Training" This course will help you to achieve excellence in this domain.

 

 

Port name           Data type   precession    sale    IOV    Expression

YOJ                                    decimal                      4                      0

To-decimal (To-chr(hiredate,'yyy')

click apply click ok

from express transformation connect to the ports to target

ex:-

design a mapping to decode the genders

0Screenshot_24M

1Screenshot_24F, unknown

Create the source table with the name customer in user account Scott

CID c name hinder

1001   cname      0

1002 cname        1

create a target table with the name T-customer (CID, Cname, hender)

the gender data type should be varchar2(1)

create the mapping with the name m-decode-gender

drop the source and target definition

create the transformation type expression

From source availed copy the ports to expression transformation double click on expression transformation select the ports tab uncheck the output port for a port name gender from toolbar click on add a new port []

Port name data type precession scale I O V     Expression

0-Gener      strain              1

decode (0.M,

1,F

'UNK')

or

IFF(Gender)=0,'M','F')

Click apply click ok

From expression, transformation connect the ports to target

ex:-

Design a mapping to calculate the year of experience from the Hire date

create a source definition with the name EMP

create a target definition with the name EMP-YOE

(empno, ename, job, hiredate, yoe, sal deptno)

The YOE data type is number (P,S)->5,2

create a mapping with the name m-employee-yoe

create the transformation type expression and develop the following type expression

Data-DIFF(SYSDATE, HIREDATE, 'YYYY')

Concatenating two string fields (comns):-

The two string fields can be concatenated using concat () function (or)ll

Recommended is 11 operator

Expression:- EFName 11 11Ename

phone :99999999

target: (999) - 9999-999

'C'II SubSTR (Phone, 1,3)II',''-'II substr (phone,4,4)II'-'

IIsubstr (Phone, 8,3)

For in-depth understanding of Informatica click on

About Author
Authorlogo
Name
TekSlate
Author Bio

TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills in the market.