Interested to Buy Any Domain ? << Click Here >> for more details...
source : col1 101 101 101 102 102 102 103 103 103
col2 1000 1500 2000 1200 2300 3000 2400 1300 2000
i need target as
col1 101 102 103
col2 1000,1500,2000 1200,2300,3000 2400 1300 2000
- 6 Answers
- 10824 Views
- CTS, Wipro, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
Answer / s
INPUT
col1 col2
101 1000
101 1500
101 2000
102 1200
102 2300
102 3000
103 2400
103 1300
103 2000
OUTPUT
col1 col2
101 1000,1500,2000
102 1200,2300,3000
103 2400,1300,2000
SOURCE->SRT->EXP->TGT
SRT - Sort by column col1.
EXP - col1
col2
v_col2=iif(v_col1=col1,v_col2||','||col2,col2)
v_col1=col1
o_col1=v_col1
o_col2=v_col2
| Is This Answer Correct ? | 11 Yes | 3 No |
Answer / john coder
i believe, we need an aggregator to pick up the last row for
a particular deptno.
Otherwise, there will be three rows inserted...
col1 col2
101 1000
101 1000,1500
101 1000,1500,2000
by having an aggregator with groupby on col1, we can pick up
the last row by either using LAST or MAX.
| Is This Answer Correct ? | 8 Yes | 0 No |
SQ-->SRT-->EXP-->AGG-->TGT
In SRT:
sort the record by col1 (Ascending)
In Exp:
col1
col2
v_col2=iif(v_col1=col1,v_col2||','||col2,col2)
v_maxcount=iif(v_col1=col1,v_maxcount+1,1)
v_col1=col1
o_col1=v_col1
o_col2=v_col2
o_maxcount=v_maxcount
In AGG:
Group by o_col1
MAX(o_maxcount)
| Is This Answer Correct ? | 1 Yes | 0 No |
Answer / venky
src->exp->agg->tgt
exp transformation
col1
col2
var1=iff(var2=col1,var1||','||col2,col2)
var2=col1
output=var1
agg transformatiom
col1 select group
| Is This Answer Correct ? | 0 Yes | 0 No |
Answer / anoymous
If the source is Relational then source qualifier can be
overridden
Select Col1, Concat('col1',',','col2') from table name
groupby col1;
| Is This Answer Correct ? | 0 Yes | 4 No |
Answer / kiran
USE SOTER TR . IN SORTER TR CLICK ON DISTINCT OPTION IT ELIMINATE THE DUPLICATE AS WELL AS SORT THE ORDER.
| Is This Answer Correct ? | 0 Yes | 4 No |
write a sql query following source? subject mark maths 30 science 20 social 80 requird output maths science social 30 20 80
What is the difference between SOURCE and TARGET BASED COMMITS?
if we have a delimiters at unwanted places in a flat file how can we over come those.
How to delete duplicate records in a flat file source?
How to update source definition?
Name the different lookup cache(s)?
Is stop and abort are same in informatica?
If I have a index defined on target table and if set it to bulk load will it work ?
Let’s say I have more than have record in source table and I have 3 destination table A,B,C. I have to insert first 1 to 10 records in A then 11 to 20 in B and 21 to 30 in C. Then again from 31 to 40 in A, 41 to 50 in B and 51 to 60 in C……So on up to last record.
In Datastage, we have row generator stage to generate dummy row. Is there any such transformation in Informatica. Thanks in advance, Kumar
source : xml file target: xml file how can we check data loaded into target xml file using writing sql query. pls help on this asap.
0 Answers Cap Gemini, Cognizant,
What are different types of transformations available in informatica?
Teradata 
