I took a look at your sample application, and for me it s working as it should. It does give you the objects that are valid for that specific date.
Maybe I am missing something... do you want to show something else?
Also, I noticed that you say:
But my logic tells me that it is the other way around, (valid from is SMALLER or equal to the key date and valid to is LARGER or eqal to the keydate)
psteinmayer wrote:meaining valid from is larger or equal the key date and valid to is smaller or eqal the keydate.
Please do let me know if I misunderstood your goal.
Thanks for the response, I should have looked closer. I am having issues in the main application and after a long night over it I was convinced that this piece of set analysis does not work, but clearly it does, this at least eliminates that part of the set analysis being the problem. Tomorrow I will investigate further....
Are you guys aware of of any other, easier way, of linking that sort of data? Interval match based on a Day Calendar creates huge linking tables especially when the theoretical end date in the master data is 31.12.9999 and start date is 01.01.1900
If you're doing it one key date at a time, then the set analysis solution you used seems fine to me. The problem comes if, say, you want a chart showing the number of valid objects for EACH possible key date, using a KeyDate field instead of a vKeyDate variable. One solution would be to replace your set analysis with an if:
count(if(validto>=KeyDate and validfrom<=KeyDate,counter))
However, if your data set is large enough to cause problems with intervalmatch, it is likely large enough to cause performance problems with this sort of count(if()).
I'm not thinking of anything other than count(if()) or intervalmatch, though. It seems like you either need to connect a KeyDate to a date range in the expression, or in the script. If in the expression, and dealing with multiple key dates at once so you can't use set analysis, I think any solution is going to be very slow to execute. If in the script, I think any solution is going to create a large data structure. But I don't see other options.
If the set analysis isn't sufficient, I'd probably try the count(if()) approach next.