logic required

[devesh_ssingh]

Hi
I have an requirment seems to be simple but stuck up with logic.

its like
i/p:
col1 col2 col3 col4(date) col5(date) col6
a b c 2010-10-20 9999-12-31 e
a b c 2010-10-21 9999-12-31 e
a b c 2010-10-22 9999-12-31 e
a b c 2010-10-23 9999-12-31 e

o/p1:
col1 col2 col3 col4(date) col5(date) col6
a b c 2010-10-23 9999-12-31 e

o/p2:
col1 col2 col3 col4(date) col5(date) col6
a b c 2010-10-20 9999-12-31 e
a b c 2010-10-21 9999-12-31 e
a b c 2010-10-22 9999-12-31 e

i need to take to record with latest effective date.

i have used followin approch:
used db(src)
|
db(src)-----aggr-----join-------records with max dt

above approch is giving me error lin agg stage as it map calculation max_DT as double datatype and hence type conversion errore

can you plesse guide me to acheive same

thanks in advance

[arunkumarmm]

I'm not sure if i'm missing something here. As far as I understood your requirement, you can try the below approach.

If your source is database, select all your rows and in a separate stage select the max of date from the same source table. Use a look-up and pass the row to target which matches and use a reject link to capture all other rows.

[stuartjvnorton]

There seems to be an awful lot of these sort of questions lately.

Sort desc on the column[s] you want to filter the output by
Create Key Change Column
Filter o/p1 where key change = 1, o/p2 where key change = 0

[BillB]

above approch is giving me error lin agg stage as it map calculation max_DT as double datatype and hence type conversion errore

Would setting the property Preserve Type = True on the Column for Calculation solve this part of the problem?

[keshav0307]

combination of sort stage and remove duplicate should work.

first hash partition on the key columns then use sort stage on key columns and the effective date Desc. and in the remove duplicate stage keep the 1st record.

[mobashshar]

Keshav.. and then how to get the remaining rows (- first row) ?

[stuartjvnorton]

Keshav.. and then how to get the remaining rows (- first row) ?

By not using Remove Duplicates.