Multiple Instances in a Lookup

[sengs]

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

[Amit_111]

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.

[DSguru2B]

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.

[us1aslam1us]

As mentioned you need to make sure what exactly is going to be latest status? In your sample data it is not clear whether you have duplicates and multiple records for same serial number and Date.

[ray.wurlod]

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);