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
- 9257 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 |
I am new to informatica and learning it,can anybody please tell me how we receive source as flat file in informatica,from where we get this flat file?
what is threshold error in informatica?
How are indexes created after completing the load process?
What is the Difference between DSS & OLTP?
How to load only the first and last record of a flat file into the target?
how lookup transformation is made active in new versions... When to use connected and when to use unconnected lookup and why? which is good for session performance. How to make lookup persistent and how to remove stale data from that lookup. how commit works - when we stop or abort data. Explain in both cases. What is factless fact table and have you ever used it in real time scenarios.
What do you mean incremental aggregation?
Hi, I want to do Informatica Designer certification. Can anyone help me by providing the details on this. Thanks in advance. Regards, Arnab
what is the end to end process meaning in my project(my project is development)and functionality plz tell me
Examples of Fatal & Non-Fatal error?
How to load dimension and fact in same mapping at same time.explain me the logic used for that scenario.
in my source i have 100 records, and 3 targets, i want to load 1st record into 1st target,2nd record into 2nd target,3rd record into 3rd target again 4th target into 1st target and vice versa,how to achieve this?
Teradata 
