Jump to content

Closest Matching of Dates


Azzuria
 Share

Recommended Posts

Please can beg for some FM18 help! I have 2 tables TPTable::date that is linked to HLTable:HL::date. I need to automate the look up of 2000 TPTable records to the closest match HLDate (80 records). I know there must be a way, but it has alluded me thus far! Thanks

Link to comment
Share on other sites

Greetings - need some more context what is unique in each table that could be used as a match to lookup data?

you could use multiple predicates in the relationship or concatenate several fields together to make a unique key to lookup data. 

 

Link to comment
Share on other sites

If you define a relationship between the two tables matching on the date field, you can use a calculation in the first table to find the closest date in the second table: use the LookupNext() function to find the nearest date before the given date; use the same function to find the nearest date after the given date; compare the differences and select the date with the smaller one.

 

Link to comment
Share on other sites

Let me try that again. I had a field cLookup_higher in my TPTable that auto calculates:

LookIpNext(HlTable::HLdate;Higher). The relationship between HLTable and TPTable is HLdate < date AND HLdate >= date. Unfortunately this did not yield a result... HLdate = date didn’t work either. 

Link to comment
Share on other sites

  • 2 weeks later...

Hi "Comment" - sorry to bother again. Closest Date not working after I changed my fields . I have replicated your relationships: closestdate = date (in my case cLookUp = HLDate) and it always returns original HLDate. Simplified I have three relationships between tables:
HLTable - TPTable -> HLID_pk = HLID_fk  this is my link to create TPTable from HLTable where I add various dates to my HLDate to derive my new cTPDate. To recap I have the original date from Excel import, HLDate, I bring HLDate to TPTable using a script and the add various dates to HLDate in TPTable to give me my cTPDate

HLTable_lookup - TPTable -> HLDate = cLookup

HLTable_date - TPTable -> HLDate = HLDate (replicating your model), it just returns my original HLDate. Also tried HLDate = cTPDate, which doesn't work same thing - not the closest match.

cLookUp is strictly your calculation whether I reference to HLTable::HLDate  it just returns my original HLDate. If I reference to HLTable_date::HLDate,  it still returns my original HLDate- not the closest match. If I reference to HLTable_lookup::HLDate or  it just crashes.

Any ideas, thanks

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.