Closest Matching of Dates

[Azzuria]

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

[Ocean West]

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. 

 

[Azzuria]

Thanks for prompt reply. I have a unique ID for each table. __TPRecordID and __HLRecordID, does that help?

[comment]

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.

 

[Azzuria]

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. 

[comment]

I am afraid I am not able to follow your description. Does the attached demo file do what you expect?

 

ClosestDate.fmp12

[Azzuria]

Thank you thank you thank you! It does. It was my relationships that was at fault. 😊

[Azzuria]

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

[comment]

I am afraid I couldn't debug your file without seeing it even if I wanted to... 🙃

 

 

[Azzuria]

Thanks anyway - I shall rebuild the tables and see what went wrong.