How to lookup for the closest value?

[kollurianu]

Hi All,

How to lookup for the closest value for time as below ,
Settle Dates are same AND
Share Quantity are same AND
Mainframe Time Stamp are same or the closest AND
The first 3 digits of the SS Asset ID for both the records are same.

Mainframe Time Stamp format is 8/20/2010 16:04

source and targets are files.

appreciate your inputs

Thanks in advance .

[PhilHibbs]

How many timestamp records are you likely to have for a given set of equal key fields? If the answer is "not many", then do a full join on the equal key fields, calculate the timestamp difference for each row of the cartesian product, sort ascending by key fields and the absolute time difference, and take the first row for each key field set.

*Update*: It probably isn't a cartesian product if one file only has one timestamp record for a key set, I kind of assumed that both files had multiple timestamp records. Not sure why I thought that.

[kollurianu]

is there any way simpler to acheive this?

Thanks for your response PhilHibbs , do you have a simpler way to acheive this ?

Thank you all in advance.

[PhilHibbs]

is there any way simpler to acheive this?

Maybe.

Thanks for your response PhilHibbs , do you have a simpler way to acheive this ?

You're welcome, but sorry, no I don't.

[kollurianu]

Hi All,

How to lookup for the closest value for time as below ,
Settle Dates are same AND
Share Quantity are same AND
Mainframe Time Stamp are same or the closest AND
The first 3 digits of the SS Asset ID for both the records are same.

Mainframe Time Stamp format is 8/20/2010 16:04

source and targets are files.

appreciate your inputs

Thanks in advance .

Any other simpler ways to approach this ? aany thoughts greatly appreciated.

Thank you all in advance.

[PhilHibbs]

Any other simpler ways to approach this ? aany thoughts greatly appreciated.

There might be a simpler way, but it will only be marginally simpler. You are going to have to perform comparisons in order to find the "closest" timestamp, so you can't get rid of the essential complexity that that involves.

[priyadarshikunal]

You are going to have to perform comparisons in order to find the "closest" timestamp, so you can't get rid of the essential complexity that that involves.

Exactly. Join on equal keys, derive the time difference and then take the one with lowest value in time difference. No direct way to get the closest value match in DataStage.

If you think about the approach, it just involves a join, transformer, sort and then remove duplicate. It may not be simplest way to do in datastage but simple enough for me to use.