Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

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.

 

Data Model.png

 

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.

 

Amount over time.png

 

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

36 Comments
Partner
Partner

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.

0 Likes
6,527 Views
Partner
Partner

Kudos to Henric!

0 Likes
6,527 Views
Not applicable

Why might this be a better option than IntervalMatch?

6,527 Views

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,527 Views
Not applicable

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.

0 Likes
6,527 Views

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

I am glad that I could help you.

HIC

0 Likes
6,527 Views