topic Re: Find key between 2 dates in QlikView

Find key between 2 dates

pauldamen — Thu, 02 Jun 2016 08:41:21 GMT

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

Re: Find key between 2 dates

sunny_talwar — Thu, 02 Jun 2016 08:49:04 GMT

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.

Re: Find key between 2 dates

pauldamen — Thu, 02 Jun 2016 09:58:06 GMT

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!

Re: Find key between 2 dates

marcus_sommer — Thu, 02 Jun 2016 11:30:36 GMT

Maybe something like this:

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

- Marcus

Re: Find key between 2 dates

swuehl — Thu, 02 Jun 2016 12:27:13 GMT

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