How to create a mapping ?
id date
101 2/4/2008
101 4/4/2008
102 6/4/2008
102 4/4/2008
103 4/4/2008
104 8/4/2008
O/P - shud have only one id with the min(date)
How to create a mapping for this
Answers were Sorted based on User's Feedback
Hi Achoudhr,
here u go,
sq--->agg----->target
In agg dont use group by on any port and take output
columns for both id & date ports, use 'min' function in
outpput expression and connect output ports to desired
target columns.you will get only one output row that is 101
2/4/2008.
note: if u use group by mapping outputs 3 rows.
Is This Answer Correct ? | 15 Yes | 6 No |
Answer / mitesh gaur
I think there is no need of using any aggregation. It's very
straight fwd, just sort the data on date and fetch only
first record.
Add the below query to source qualifier's SQL OVERRIDE
and that will be your result - (lets say table name is TmpTab)
select * from (select * from TmpTab order by date) where
rownum = 1;
now connect id, date to target.
Is This Answer Correct ? | 9 Yes | 5 No |
Answer / rob
Add the below query to source qualifier's SQL OVERRIDE
and you will get the required output
select * from (select min(to_date(date,'dd/mm/yyyy'))as
d,id from emp
group by id order by d)where rownum =1;
source-->SQualifier(with query)----> target
Is This Answer Correct ? | 2 Yes | 1 No |
Answer / parthiban
S->SQ->SRT->EXP->FIL->TGT
Sort on date Column
In Expression, Create variable ports for partition
V_Currdate = date
V_Flag = IIF(V_Currdate = V_Prevdate, V_Flag+1,1)
V_Prevdate =date
O_Flag = V_Flag
In Filter, Filter where O_Flag =1
Is This Answer Correct ? | 2 Yes | 2 No |
Answer / azhar
Simple use sql override query:
select top1 * from TABLENAME order by ID,DATE
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / deepak
SQ --> Sorter (sort on date desc) --> Aggregator (select no ports so it will always return last row) --> Target
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / bidhar
I think you can get this by SQ Override query itself.
SQ->Exp->Target
SQ Override Query:
SELECT ID,MIN(DATE) FROM TABLENAME GROUP BY ID.
Let me know if i am wrong.
Is This Answer Correct ? | 2 Yes | 4 No |
Answer / kalyan
I guess the below logic will solve this question..
Sq -> Expression T/R -> Aggr t/r -> Target
Create a constant port in Expression t/r and then pass the 3
fields id, date & const port to the aggregate t/r... Create
a new port calculating mindate.. Now do a group by on the
Const field ; connect id & min Date fields to the Target...
FYI, If u don't apply group by on any column of the table ;
can not calculate the Aggr values...
Please correct me if I am wrong...
Is This Answer Correct ? | 1 Yes | 7 No |
when we go for unconnected lookup transformation? and why?
1:when we develop a project what are the performance issue will raise?? KPIT 2:if a table have INDEX and CONSTRAINT why it raise the performance issue bcoz when we drop the index and disable the constraint it performed better??KPIT 3:what are unix commands frequently used in informatica??
How does the aggregator transformation handle null values?
Hi, I have one scenario.. i want to insert 4 times (Duplicate) a row in the target. Please help me . source row: abcabc Tgt table: abcabc abcabc abcabc abcabc
Give some information on report bursting and how to do it in bca as I have to split the report and send different reports to different people?
Which transformation should we use to normalise the COBOL and relational sources?
How to generate or load values in to the target table based on a column value using informatica etl tool.
what is confirmed dimension?
we r using aggregator with out using groupby?
7 Answers HSBC, Principal Finance,
What is the Process to creating lookup transformation in informatica
What is the difference between a router and a filter transformation?
write a query to follwoing table amount year quarter 254556 2003 first 546332 2003 second 129034 2003 third 490223 2003 fourth 165768 2004 first 265443 2004 second 510412 2004 third 435690 2004 fourth i want the output year q1_amount q2_amount q3_amount q4_amount 2003 254556 546332 129034 490223 2004 165768 265443 510412 435690