Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Valid from & Valid to Set Analysis see Example attached

Dear All,

I working on an application with multiple layers of information which besides the usual keys all have a valid from and valid to date. Each layer of information can change from one day to the other.

In order to be able to retrieve the correct set of information for one particular keydate i will have to check if the object is valid on that particular keydate and narrow down accordingly. This would have to be done across all related layers of data involved in the analysis to get the correct result.

I have looked at other options such as the intervals function, please do focus your answers on this particular way of getting the job done. Attached you will find an application demonstrating the problem with 1 layer of data, if this works i could replicate the set analysis across all other layers to get the correct result.

The aim is to enter a key date and for the report to show only objects which are valid on that particular key date, meaining valid from is larger or equal the key date and valid to is smaller or eqal the keydate. This should in theory provide the correct result. I could in this case run the same set analysis accross all Valid From and Valid To dates in all related tables and narrow down to just retrieve information that is Valid on the selected day.

Thanks for your thoughts on this

Patrick

3 Replies
mike_garcia
Luminary Alumni
Luminary Alumni

Hello Patrick,

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:


psteinmayer wrote:meaining valid from is larger or equal the key date and valid to is smaller or eqal the keydate.
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)

Please do let me know if I misunderstood your goal.

Mike.

Miguel García
Qlik Expert, Author and Trainer
Not applicable
Author

Hi Mike,

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

Best Regards,

Patrick

johnw
Champion III
Champion III

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.