Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
hic
Former Employee
Former Employee

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

47 Comments
hic
Former Employee
Former Employee

You should create one master calendar per date:

Why You sometimes should Load a Master Table several times.

And, in addition, you should create a Canonical date that can represent any date:

Canonical Date.

You probably also want to measure the time it took to treat a Claim, by calculating

  [Arriving Date] - [Treatment Date] as Delay. See more on Buckets.

HIC

0 Likes
6,583 Views
Not applicable

Hi Henric,

This indeed is an incredible use of reference dates for interval.  I must also add that I have applied your  suggestions on the other application – and it works superbly. Also I would like to share a free resource on qlikview which is very useful : https://www.youtube.com/watch?v=8olMt2AOUJ8

0 Likes
6,583 Views
Not applicable

@Henric..and Others...I am new to Qlik. Can you help me here please? I have Transaction data and Discount tiers. I want to match both and would like to have desired results as described below..

I placed complete file in my brand new question...

Need help in computing Discounts based on Min and Max limits (by Customer)

Thanks for your help.

Transaction Data sample:

C_YearC_QtrDateProductCustomer NumberQuantityMember Type
2014412/1/2014Apple1000140AAA
2014412/1/2014Orange1000155AAA
2014412/1/2014Banana1000175AAA
2014412/1/2014Apple1000260BBB
2014412/1/2014Orange1000263BBB
2014412/1/2014Banana1000244BBB
2014412/1/2014Apple1000349CCC
2014412/1/2014Orange1000354CCC
2014412/1/2014Banana1000359CCC
2014412/2/2014Apple1000140AAA

Discount Tiers Data (Sample):

C_YearC_QtrProductDiscount_TypeMember_TypeTierStartEndDiscount%
20144AppleGAAAG_Tier_00340
20144AppleGAAAG_Tier_13549.91
20144AppleGAAAG_Tier_25064.992
20144AppleGAAAG_Tier_3659999993
20144AppleGBBBG_Tier_13549.91
20144AppleGBBBG_Tier_25064.992
20144AppleGBBBG_Tier_3659999993
20144AppleGCCCG_Tier_110252
20144AppleGCCCG_Tier_226503

Desired Output:

C_YearC_QtrProductCustomer NumberMember TypeQuantity (Sum of Quarter)Discount_TypeTierDiscount%
20144Apple10001AAA80GG_Tier_33
20144Orange10001AAA150GG_Tier_33
20144Banana10001AAA110GG_Tier_33
20144Apple10002BBB120GG_Tier_33
20144Orange10002BBB88GG_Tier_33
20144Banana10002BBB126GG_Tier_33
20144Apple10003CCC98GG_Tier_33
20144Orange10003CCC118GG_Tier_34
20144Banana10003CCC108GG_Tier_34

0 Likes
6,583 Views
Anonymous
Not applicable

Hello Henric

  am using a combination of Canonical Calendar and the above calendar concept to calculate missing dates.need to show a trend of outstanding tickets across one common month axis.

Outstanding ticket count as of 28 Feb = all tickets with blank [Resolution Date],[Resolved Date] and [Resolve date UTC] while being opened in Feb or prior to Feb. +

Tickets that were opened in Feb or prior to Feb but resolved after 28 Feb.

Challenge that I am facing is that even if I use set analysis and flags while counting the outstanding tickets I am getting performance issues while clicking the listbox. Numbers get populated correctly but it is slowing down the application. Can you suggest what else should i consider while changing my script?

0 Likes
6,583 Views
Not applicable

Hi Henric,

I have 450,000 rows of records with dozens of fields, the performance is already slow with just my raw dataset. Is there a less expensive method that you would recommend? Creating a unique Policy ID x reference date table for each record seems like it would crash the app.

0 Likes
6,559 Views
anderseriksson
Partner - Specialist
Partner - Specialist

If you have performance problems with only 450 000 rows and some dozen fields
then you have some other serious issue with your application and data modell.
On reasonably sized hardware that amount of rows should not be a problem.

6,559 Views
datanibbler
Champion
Champion

Hi,

that is indeed an excellent approach.

I think IntervalMatch() is pretty straightforward - you have to do a bit of thinking because the integrated help_file does not stick with one example for the simple and the advanced syntax, but it should not be a problem to think your way through it.

It does take longer than just keeping the table with just a start- and an enddate and using IntervalMatch - I just have a scenario and I also went for a loop generating one record for every day, but it takes quite a while - but since IntervalMatch can only deal with the intervals on one side - in one of the two tables there have to be discrete datapoints to fit in the intervals - I needed to do this for one of the two tables involved.

>>> What I don't quite understand - what is the advantage of having this three-tables-scenario instead of just "pumping up" the one table - that would be the "policies" table to stick with your example? (there would have to be one or two counter_tables,  but those can be deleted once the loop has completed, so that doesn't matter)

Thanks!

Best regards,

DataNibbler

0 Likes
6,559 Views
Anonymous
Not applicable

Hi..

I have a similar request to show all the dates and multiple KPIs with it.

I have used a combination of canonical calendar and reference dates approach and I have noticed that as the months start increasing the time to execute the file takes long and it slows down the performance. Do you have any other solution for it?

0 Likes
6,559 Views
Anonymous
Not applicable

 Hi Henric,

In the fact table I have 4 Date : CMFrom,CMTo,LCMFrom,LCMTo.

and In Date Range have 2 Date : FromDate, ToDate

i have multiple condition :

ToDate    >=LCMFrom and

                   <=CMTo  and

                    >=CMFrom  

FromDate    <= CMFrom  and

                        <=  LCMTo

can you please suggest something about this.

i am thinking to create 2 more date field in fact table just to create interval(TempMaxDate & TempMinDate) :

Inteval1 : ToDate    :

                    >=LCMFrom and

                     <=CMTo 

Interval2 :ToDate:

                               >=CMFrom   and 

                                  <=TempMaxDate (can set it to 2020 something)

Interval3 :FromDate:

                                       <= CMFrom  and

                                          >=TempMinDate (can set it to 1900 something)

Interval4 :FromDate:

                                       <= LCMTo and

                                          >=TempMinDate (can set it to 1900 something)

 

uptill now i have implemented 2 intervals  and its taking so much time to reload , i dnt know what will happen if i add 4 interval.

 

 

 

 

0 Likes
6,496 Views
ajayvermaida
Partner - Creator
Partner - Creator

Hi @hic 

In one of my issue I have to count the time diff between two Dates like below

1- First I have to show the time only weekdays in between 7 am TO 11 pm EXCLUDING holidays and Sat and Sunday.

2- Calculate the time for Sat and Sunday in between 10 AM to 4PM and added with point 1.

3- If any ticket comes in Sat 2 PM and closed in week days then count the time based on there time range like Sat and Sun we have support only 10 AM to 4 PM so time of this ticket is Sat = 2 hours and Sunday 6 Hours . if the ticket closed in Tuesday 3 PM then weekend time diff is Monday 16 hours as we have the 16 hours support in weekdays (7 AM to 11 PM) and Tuesday = 8 hours so total time spent is weekend = 8 hours + weekdays = 24 Hours total =32 hours

4- If ticket open this month and closed in next month then time calculation whatever time spent in current month and rest comes in next month.

please suggest how can I do that

 

0 Likes
1,923 Views