Select between Valid_from and Valid_to

[#1]

Hi everybody,

I got a Datawarehouse, STAR Schema, connected to QlikView. Everything is connected pretty good and it works fine so far. There is only one thing I dont really know how to handle it properly.

In the Dimensiontable D_CASES we got the ID, STATUS, VALID_FROM and VALID_TO (and others which are not necessary in this case). Now I need to know, for example, the Status of all ID's at a specific date, like the 4th of january 2010. Thats all normal DWH logic so I guess someone out there can give me an advice how to handle it.

The Table has aprox. 32 Mio. records (growing) and the facts connected to this table is aprox. 55 Mio. records (growing), so it's a performance issue too.

I thought about using SET where the selected Date is a variable used to get the Valid records for each ID. Or maybe build a lookup for all possible combinations of VALID_FROM/TO and days...
I would prefer to somehow filter all valild records than just "ignoring" them through SET.

I'm glad for every Solutions/Ideas/Help

QlikView Version: 8.5

Thanks in advance and Kind Regards

[#2]

You could use an EXTENDED INTERVALMATCH to get all dates between VALID_FROM and VALID_TO per ID, but it would really add to your number of records.

Never use the Extended version before, but it should look something like this.

Temp:
INTERVALMATCH (date, ID)
LOAD
 VALID_FROM,
 VALID_TO,
 ID
RESIDENT D_CASES;
JOIN (D_CASES)
LOAD
 VALID_FROM,
 VALID_TO,
 ID,
 date
RESIDENT Temp;
DROP TABLE Temp;

[#3]

Sounds good, except that I dont know if it will work with that many records. I'll try and let you know - this could take while indeed 😉

So far I made a "workaround" with If expressions, works fine but, as mentioned, I'd prefer to select these cases so I'll try it for sure!

Many thanks and

Kind Regards