Inspired by Henric's post http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/05/populating-a-sparsely-populated-field I've created a generic routine to handle many cases of expanding an interval table like in the post. I plan to include this in the next release of Qlikview Components. In the meantime, I'm looking for some feedback if anyone has time to try it.
Attached is some doc and a reloadable sample qvw. If you want to try it with your own data, you need only copy the script from the ExpandRange script tab into your qvw.
All feedback welcome, especially use cases where it does or does not work. Also looking for a better name than "ExplodeRange".
As it patches the holes in intervals....how about "IntervalPatch"?
And how about making it possible to set a step size? For example to generate dates for every weekstart or monthstart instead of every day? Actually, would it be hard to generalize it for other numeric intervals besides dates? I can see this being quite useful for dealing with missing values in other kinds of ranges.
Great work, btw! I'm looking forward to the next qvc release.
Thanks for the feedback. It should work as-is on any numeric range, I just happened to be using dates in the example. I should work up an example for non-dates.
Can you describe the use case where you would want to use a step size? If you link a master calendar in to the Date field, you can count things like weeks and months.
Very nice, Rob. I will experiment and test this week. I can imagine many applications. For example, I've worked with datemodels where employee goals must be distributed in a similar way (based on their hire date and optional term date). Or another where project budet amounts must be spread from the start date to the end date, etc.
I'll follow up this week after kicking the tires a bit. As for the name, how about DistributeRange? Or maybe a variation of Gysbert's idea that involves intervals, perhaps IntervalDistribute?
Yes, that's one case. A similar one is where reporting on projects is done not per day, but say per two weeks. And for some projects (or project activities) there are no records for every period, so there are gaps that should be filled with values being carried forward.
Another would be where activities are registered per shift with a number of shifts per day. This would involve creating shifts for a period based on a start timestamp and end timestamp. Ok, one could do that by first multiplying the dates by the number of shifts per day and afterwards dividing again by that number. But being able to specify a step, e.g 1/3 for three shifts per day, would make things a bit easier.
Thanks for the feedback. The routine as it now stands does not do distribution (as I think if it). That is, it does not spread the values over the range. (Value / (Start-End)). It associates whatever field values are in the input table to each date in the range.
Does that fit with your use case? I think as is, for something like a budget amount it may create difficult to use results. For eample, given a Budget amount of $400 for the interval, a sum(Budget) for 5 days would yield $2000 which would be incorrect.
Yes, good point. I'm thinking cases where you know the start/end date, and can calculate the distribution (or allocation may be a better word). For example, budget hours for Project records may be annual, so if loading a fact table with monthly records, you can just calc as Tot/12. Or if the amount is not annual, but rather a total for the Project, then you can calc the # periods in the Project, and calc as Tot/# periods.
Or maybe for employees that have an annual goal, but you want to faciliate charts or analysis on a weekly basis. The way I've done this in the past is to append records to a Fact table that include the periodic (i.e., weekly) goal amounts for each employee. But that requires essentially using a date range and then allocating the amount over as many records as required. It actually gets more complex sometimes, as the employee amounts (budget or target or whatever) can have multiple changes, not just a single start and end date.
But I'm thinking your routine may save some time with these types of operations.