Skip to main content
Announcements
YOUR OPINION MATTERS! Please take the Qlik Experience survey you received via email. Survey ends June 14.
cancel
Showing results for 
Search instead for 
Did you mean: 
pauldamen
Partner - Creator II
Partner - Creator II

Find key between 2 dates

Hi,

I have a table with products which have an open date and a close date for example:

204939420     1/1/2016     4/1/2016

What I need is when I select 3/1/2016 it counts this one as an open record. Normally I would do an interval match in the script but my table conistst of 20 mln records over 6 years. So doing an intervalmatch will result in Qlikview crashing because the result is an endless amount of rows.

How can I solve this? Is there another way in the script? Or better to solve in a formula in the front end? And what would that formula be ?

Thanks!

Regards, Paul

4 Replies
sunny_talwar

You can use this way

LOAD ID,

          Date(StartDate + IterNo() - 1) as Date

While StartDate + IterNo() - 1 <= EndDate;

LOAD ID,

          StartDate,

          EndDate

From Source;

But to tell you the truth, I have no idea if interval match or this is any better to each other.

pauldamen
Partner - Creator II
Partner - Creator II
Author

Hi Sunny,

I wasn't any faster then the intervalmatch. Because it is just to much data.

What we now did instead of an intermatch on everyday we limited it to a monthly intervalmatch with the last 2 months on a daily basis. This limits the amount of data so we "only" have 350 mln records as a result

Thanks for the help anyway!

marcus_sommer

Maybe something like this:

count({< Opendate = {">=SelectedDate"}, Closedate = {"<SelectedDate"}>} Product)

- Marcus

swuehl
MVP
MVP

Hi Paul,

I think what you've already done now is similar to what John Witherspoon has described here:

While loop for creating dates between 1985 and 2016 - performance issues

Maybe you get some more tips & tricks from that post.

Regards,

Stefan