Measure on an interval in a SCD Table

qw_jakub — Thu, 19 Sep 2013 12:28:37 GMT

Hello,

What is the best way to compute a measure on data from an SCD table?

I know how to compute a measure when a fact table is involved, but am having a trouble in extracting information from the SCD table itself.

Let's say the columns are like this:

Interval_ID

Category

start-active

end-active

FromDate

ToDate

and i need to find out how many IDs were active (in the interval between start-active and end-active) in a given time period.

That is I need to pick the valid records for the period based on [FromDate], [ToDate] and then count the active IDs based on [start-active], [end-active].

Any tips on how to achieve this in the load script and/or expression would be very helpful.

See the attached qv file for my test data. Thanks!

ID	Interval_ID	Category	start-active	end-active	FromDate	ToDate
1	1	A	28.4.2010	2.5.2010	1.1.1990	3.5.2010
1	2	A	4.5.2010	4.6.2010	4.5.2010	9.5.2010
1	3	B	4.5.2010	4.6.2010	10.5.2010	31.1.2099
2	4	A	20.4.2010	15.5.2010	1.1.1990	8.5.2010
2	5	B	20.4.2010	15.5.2010	9.5.2010	20.5.2010
2	6	B	21.5.2010	1.6.2010	21.5.2010	31.1.2099
3	7	B	20.4.2010	3.5.2010	1.1.1990	9.5.2010
3	8	B	10.5.2010	25.5.2010	10.5.2010	15.5.2010
3	9	A	10.5.2010	25.5.2010	16.5.2010	28.5.2010
3	10	A	29.5.2010	4.6.2010	29.5.2010	31.1.2099
4	11	A	20.4.2010	5.5.2010	1.1.1990	7.5.2010
4	12	A	8.5.2010	25.5.2010	8.5.2010	15.5.2010
4	13	B	8.5.2010	25.5.2010	16.5.2010	20.5.2010
4	14	B	27.5.2010	5.5.2010	27.5.2010	31.1.2099
5	15	B	4.5.2010	4.6.2010	1.1.1990	31.1.2099

topic Measure on an interval in a SCD Table in QlikView

Measure on an interval in a SCD Table