Hi,
I have the following requirements
Input:
S.No Name
1 a
2 b
3 c
Lookup:
S.No Status Date
1 Active 03/16/2007
2 Inactive 03/16/2007
1 Inactive 03/16/2007
2 Active 03/16/2007
1 Active 03/16/2007
When i do a lookup i want to take the latest status corresponding to a S.NO and write that into target.
I cannot insert a new column into the lookup table.
If there are any work around pls let me know.
Thanks
Multiple Instances in a Lookup
Moderators: chulett, rschirm, roy
How are to going to find out which is the latest status for the same date since ther is no time field in it (LookUp)??
One way can be that you can write a query in the Database stage itself i.e. (Lookup table stage) and select only the latest records from there and then lookp up with the Input and then insert it in the target.
One way can be that you can write a query in the Database stage itself i.e. (Lookup table stage) and select only the latest records from there and then lookp up with the Input and then insert it in the target.
Where is your lookup coming from? Is it a database or a flat file?
If database, then write sql to select max date and status grouping on S.No. Load the result to a hashed file keyed on S.No.
Now your hased file contains the latest status for a S.No.
From here on do the normal lookup against the hashed file.
If database, then write sql to select max date and status grouping on S.No. Load the result to a hashed file keyed on S.No.
Now your hased file contains the latest status for a S.No.
From here on do the normal lookup against the hashed file.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
User defined SQL
Code: Select all
SELECT S_NO, STATUS_DATE FROM TABLENAME T1, TABLENAME T2 WHERE T1.S_NO = T2.S_NO AND T1.S_NO = ? AND T1.STATUS_DATE = (SELECT MAX(T2.STATUS_DATE) FROM T2);
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.