Find key between 2 dates

[pauldamen]

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

[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]

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]

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