problem in getting unique record

[harithay]

Hi all;

i have source flat file(tab delimited). i have to calculate ' change in assets' for new records based on existing records based on fund name , fund_no(key columns) after matching date difference between new record and existing record should be 360 and 370,and the existing record should have the 'net assets' column maximum.


the key column in table in database is account_id (sql server auto generated)


after i get the record from database (with same fund_name, fund_no, date difference lies between 360 and 370, with 'assets' of maximum value)
i have to caliculate 'change in assets' for new record by subtracting ASSETS (new record) - assets (old record).

i don't know how to get record from database. but 'Hashed file stage ' does not work here (because i can get only one matching record with 'assets 'maximum but after that it may not satisfy date differnce between 360 and 370 days).

please i need some suggetsions how to proceed.

is there any possibility first checking date differnce (360 and 370 days) and after that taking only one record with max 'assets' from database


Thanks in advance

[ray.wurlod]

Use an ODBC stage to connect to SQL Server. In the Transformer stage check the "multiple rows for lookup" check box on the link properties.
Read the on-line help topic covering multiple rows for lookup, or search this forum where it's been described on a number of occasions.