• USA : +1 973 910 5725
  • INDIA: +91 905 291 3388
  • info@tekslate.com
  • Login

Variable Ports in Informatica

Variable Ports in Informatica

A port which can store the data temporarily is know 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 portsare 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    DatatypePrecession ScaleI O V  Expression
V-total saldecimal72 5IF(isnull(comm)), sal, sal+comm)
V-HRAdecimal72 5sal*0.14
V-Taxdecimal72 5sal*0.12
total saldecimal72 Screenshot_6V-total sal
HRAdecimal72 Screenshot_6V-HRA
Taxdecimal72 Screenshot_6V-tax
Gross saldecimal72 Screenshot_6V-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 transformat select the ports tab from tool bar  click and add a new port (:)

These core tutorials will help you to learn the fundamentals of Informatica. 
For an in-depth understanding and practical experience,  explore Informatica Training Online.

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 un check the output port for a port name gender from tool bar 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 year of experience from 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 indepth understanding of Informatica click on

Summary
Review Date
Reviewed Item
Variable Ports in Informatica
Author Rating
5

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at info@tekslate.com, we will update the article in 24 hours.”

0 Responses on Variable Ports in Informatica"

    Leave a Message

    Your email address will not be published. Required fields are marked *

    Site Disclaimer, Copyright © 2016 - All Rights Reserved.

    Support


    Please leave a message and we'll get back to you soon.

    I agree to be contacted via e-mail.