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
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 |
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 |
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 |
pmscmd startworkflow -sv <host name> -d <domain name> -u <user name> -p <password> -f <folder name> <workflow name> while running this command I got the error as "no gateway connectivity is provided for your domain name". So anyone please help me how to rectify this error?
hw to load this give the mapping? cty state o/p c1 s1 c1 c1 s2 s1 c1 s1 c1 c2 s3 s2 c3 s4 c1 c3 s2 s1 c2 s3 c3 s4 c3 s2 2 columns should be loaded to one column in target table?
Please explain in detail with example about 1.Confirmed Dimension. 2.Junk Dimension. 3.Degenerated Dimension. 4.Slowly changing Dimensions
What is different between the data warehouse and data mart?
What are the designer tools for creating tranformations?
my session have to run Monday to Saturday not run sunday how to sedule in informatica level?
1 Answers Infinite Computer Solutions,
What is a stored procedure transformation?
How did you handle performance issues If you have data coming in from multiple sources, just walk thru the process of loading it into the target
Using Expression transformation how to remove duplicate records from a relational source?
What is query panel?
What are the components of workflow manager?
In what scenario we use to improve session performance by pushdown optimization?can any one give example?