Source table
-------------
ID NAME
--- ------
101 PANKAJ
NULL KUMAR
NULL MATHUR
102 JYOTI
NULL SAXENA
103 SACHIN
NULL TENDULKAR


TARGET TABLE
-------------
ID NAME
--- -------
101 PANKAJ KUMAR MATHUR
102 JYOTI SAXENA
103 SACHIN TENDULKAR


How to do the above scenario in Informatica.

Answers were Sorted based on User's Feedback



Source table ------------- ID NAME --- ------ 101 PANKAJ NULL KUMAR NULL MATHUR 102 ..

Answer / babu

Hi Sbvprasad,
Your answer has small correction,

V1=iif(not isnull (id),id,v1)

for the 1st record.
V1= iif( not isnull (101), then pass 101).
now the V1 variable having the value = 101.

2nd record
V1= iif( not isnull ( null), then pass else condition V1 (
having 101 ))
now the V1 variable having the value = 101.

3rd record
V1= iif( not isnull ( null), then pass else condition V1 (
having 101 ))
now the V1 variable having the value = 101.

After applied the above logic we will get the output as
mentioned below.
-------------
ID NAME
--- ------
101 PANKAJ
101 KUMAR
101 MATHUR
102 JYOTI
102 SAXENA
103 SACHIN
102 TENDULKAR

2.Take an EXP t/r,in this t/r declare two variable ports
for ID and Name columns.those are PRV_ID and VAR_NAME

VAR_NAME=IIF(PRV_ID=ID,VAR_NAME||' '||NAME,NAME)
PRV_ID=ID
OP_NAME=VAR_NAME (OUTPUT PORT)

3.In the next step take the aggregator transformation and
group by on the ID COLUMN. Then you will get the result.

TARGET TABLE
-------------
ID NAME
--- -------
101 PANKAJ KUMAR MATHUR
102 JYOTI SAXENA
103 SACHIN TENDULKAR

MAPPING ::
---------

SRC-->SQ-->EXP-->EXP-->AGGR-->TRG
IF ANY THING WRONG, PLEASE CORRECT WITH PROPER SOLUTION.

Is This Answer Correct ?    2 Yes 0 No

Source table ------------- ID NAME --- ------ 101 PANKAJ NULL KUMAR NULL MATHUR 102 ..

Answer / nagraj

From SQ pass to Expression Transformation

- Take Variable for ID i.e.
V_ID =iif(not isnull(ID),ID ,V_ID )
- Take Variable for Name i.e.
iif( isnull(ID) ,CONCAT(CONCAT(LTRIM(RTRIM
(V_old_Name)),' ') ,Name),Name)

At this stage you will see the Data like this
ID Name
101 PANKAJ
101 PANKAL KUMAR
101 PANKAJ KUMAR MATHUR

Now pass the ID and NAME output port through an Aggregator
Transformation
-Take new variable for Name i.e.
New_Name=concat(concat(New_Name,' '),Name)
-Take new output port for Name and put expression for the
Name output port as follows
Out_Name=GREATEST(Name,New_Name)
- Check Group By on ID

Pass the ID and Name outpurt port to the Target.

option (truncate and load)



Where Group by on ID port

Is This Answer Correct ?    1 Yes 0 No

Source table ------------- ID NAME --- ------ 101 PANKAJ NULL KUMAR NULL MATHUR 102 ..

Answer / user

just take a variable port

then write the condition
decode(id,null,prev_id,id)

prev_id =id(it should be a variable port)

i m just giving an idea

Is This Answer Correct ?    0 Yes 1 No

Source table ------------- ID NAME --- ------ 101 PANKAJ NULL KUMAR NULL MATHUR 102 ..

Answer / sbvprasad

V1=iif(not isnull (id),id,v1)

for the 1st record.
V1= iif( not isnull (101), then pass 101).
now the V1 variable having the value = 101.

2nd record
V1= iif( not isnull ( null), then pass else condition V1 (
having 101 ))
now the V1 variable having the value = 101.

3rd record
V1= iif( not isnull ( null), then pass else condition V1 (
having 101 ))
now the V1 variable having the value = 101.

After applied the above logic we will get the output as
mentioned below.
-------------
ID NAME
--- ------
101 PANKAJ
101 KUMAR
101 MATHUR
102 JYOTI
102 SAXENA
103 SACHIN
102 TENDULKAR

2.In the next step take the aggregator transformation and
group by on the id. Then you will get the result.

TARGET TABLE
-------------
ID NAME
--- -------
101 PANKAJ KUMAR MATHUR
102 JYOTI SAXENA
103 SACHIN TENDULKAR

Is This Answer Correct ?    3 Yes 8 No

Post New Answer

More Informatica Interview Questions

What is the need of an ETL tool?

0 Answers   Informatica,


Briefly describe lookup transformation?

0 Answers  


What are the mapings that we use for slowly changing dimension table?

2 Answers  


Source is a flat file and want to load unique and duplicate records separately into two separate targets; right??

4 Answers  


i have one table like cust_id ph1 ph2 ph3 1002 9290123526 97671927210 9876545232 we use Normalizer wt is o/p? sue normalizer we can generate three tables in single table/

4 Answers  






what are the types of facts with Examples?

3 Answers  


we have three columns and two rows. col1 col2 col3 a b c want to change into 2 columns and 3 rows ,how? col1 a col2 b col3 c which transformation u'll use and how?

3 Answers   Cognizant,


Informatica and datawarehousing courses in Pune?

2 Answers  


How to add source flat file header into target file?

3 Answers   Logica CMG,


What is source qualifier?

6 Answers   SDS,


Define joiner transformation?

0 Answers  


S1 is having 1 lakh records and s2 is having 100 records, s2 should compare s1 if emp no is same data should be updated if not their it should insert the data.what are the transformation used?

5 Answers   TCS,


Categories