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
- 10821 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 |
Hi All, I've 110 records in my table but 101 records contains an error. When I run the session, I want to load the 100 records into the target. Can anyone suggest me the best solution for this... Thanks in Advance,
What is depict expression change?
what is the difference between informatica6.1 and infomatica7.1
Please let me know how to do estimation before staring development in project. Here estimation in the sense how many associates are required, etc to complete the project.
How will you display 10-15 letters from a name? (for ex: name="sivasubram'aniam'ramakrishnan". o/p wanted="aniam")
I have 10 columns in a flat file and 10 rows corresponding to that columns. I want column number 5 and 6 for last five records. In unix as well as informtica.
can we override a native sql query within informatica? How do we do it?
In what scenario we use to improve session performance by pushdown optimization?can any one give example?
i want to load data in to two targets..one is dimension table and the other is fact table?how can i load ata a time
Global and Local shortcuts. Advantages.
How can i send first half of the records to one target and Remaining to other target?
What is a code page?
Teradata 
