Creating Reference Dates for Intervals

[hic]

Last week I wrote about how to create reference dates for exchange rates and warehouse balances: How to populate a sparsely populated field.

There is however also a second case where you want to create reference dates, but the data model is quite different. It is when you have a list of contracts with validity periods: Rental contracts, Insurances policies, Healthcare commitments, etc.

Each contract has a begin day and an end day. The analysts of an insurance company would probably want to ask the question: “How many valid insurance policies did we have on this specific day?” In other words, he wants to click on a reference date to see the count of policies that are associated with this date – even though this date doesn't exist in the source data.

The solution is to first load all policies in one table. Then load a second table that has one record per policy and date (Policies_x_Dates). This second table can in turn link to a master calendar.

But the middle table does not exist in the source database. Instead, you must generate it using a While loop that loops over each record in the Policies table, i.e. each source record will be loaded several times. This way, you can load not only the “From” date and the “To” date, but also all dates in between:

Policies:
Load PolicyID, BirthDate, PolicyAmount, FromDate, ToDate, OtherPolicyAttribute
          From Policies;

Policies_x_Dates:

Load PolicyID,
          Age( FromDate + IterNo() – 1, BirthDate ) as Age,
          PolicyAmount / (ToDate - FromDate + 1) as DailyAmount,
          Date( FromDate + IterNo() – 1 ) as ReferenceDate
          Resident Policies
          While IterNo() <= ToDate - FromDate + 1 ;

Note that the Policies table has exactly one record per insurance policy, and the newly created Policies_x_Dates table has exactly one record per combination of policy and date. Note also that there are other fields that should be put in the Policies_x_Dates table, e.g., the age of the insured person, since this depends on the reference date. Further, it is possible to break up a cost or an income into daily amounts, which is useful when you want to show the correct amount distributed over the year.

The While loop is a very useful tool whenever you need to create additional tables in the data model. It is often a better option than the IntervalMatch.

Read more in Generating Missing Data In QlikView.

HIC

[avastani]

Excellent post Henric!

This gives me a whole new way of doing things I previously took a long time to do in scripts and create redundant statements. I guess we could extend this into Cumulative Running Total for inventory as well.

[maksim_senin]

Kudos to Henric!

[#4]

Why might this be a better option than IntervalMatch?

[hic]

Don't get me wrong - intervalmatch is an excellent solution, If you have applications that work and that use intervalmatch, you should keep them as they are.

It is just that a solution using a While loop is sometimes more straightforward and easier to understand than a solution based on intervalmatch. (You need to make a script that someone else can read and manage...)

For example: If you solved the example above using intervalmatch, you could not as easily create the fields Age and DailyAmount in the IntervalMatch table.

Further, in situations where you have slowly changing dimensions, e.g. in a case where the salesperson changes department and you need to create a composite key based on both date and salesman, the intervalmatch solution is slightly more complex than a solution using a While loop.

HIC

[#6]

Hi Henric,

this post is really helpful considering what I've been trying to solve at the moment:

I've some entities with a starting date and a closing one, and a state, and I've to count a penalty for each day after the second between the starting and the closing date. (i.e. if it started on April 1st and closed on April 10th, I've to count a penalty for each day between April 4th to April 10th). Then I'll have to graph these penalties on a daily bases to see how much the total grows up day by day.

[hic]

Sounds like a while loop will solve the problem easily. Good luck!

I am glad that I could help you.

HIC

[#8]

Hi Henric

Great worked well for me this option as opposed to interval match but I do have a SYN table..should that be expected?

Thanks

David

[hic]

No, normally you should not have a synthetic key. Could it be that you have included more than one one field from the contracts table (or equivalent) when you make the While loop?

HIC

[#10]

Your right Sorry..should have worked that out myself!! now sorted thanks

[#11]

Thank you Henric!  Using this in a solution for a client that is not using a master calendar linked to the data model.