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 .
How to lookup for the closest value?
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 614
- Joined: Fri Feb 06, 2004 3:59 pm
-
- Premium Member
- Posts: 1044
- Joined: Wed Sep 29, 2004 3:30 am
- Location: Nottingham, UK
- Contact:
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.
*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.
Phil Hibbs | Capgemini
Technical Consultant
Technical Consultant
-
- Premium Member
- Posts: 614
- Joined: Fri Feb 06, 2004 3:59 pm
-
- Premium Member
- Posts: 614
- Joined: Fri Feb 06, 2004 3:59 pm
Re: How to lookup for the closest value?
kollurianu wrote: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.
-
- Premium Member
- Posts: 1044
- Joined: Wed Sep 29, 2004 3:30 am
- Location: Nottingham, UK
- Contact:
Re: How to lookup for the closest value?
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.kollurianu wrote:Any other simpler ways to approach this ? aany thoughts greatly appreciated.
Phil Hibbs | Capgemini
Technical Consultant
Technical Consultant
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
Re: How to lookup for the closest value?
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.PhilHibbs wrote:
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.
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.
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.
Genius may have its limitations, but stupidity is not thus handicapped.