Creating a Date Interval from a Single Date

[hic]

Sometimes when you load data into QlikView you have validity ranges, but the range is only implied by one field – a single change date.

It could be like in the table to the right where you have currency rates for multiple currencies: Each currency rate change is on its own row; each with a new conversion rate. Also, the table could contain rows with empty dates corresponding to the initial conversion rate, before the first change was made.

This problem is very similar to the one in a previous blog post (How to populate a sparsely populated field) but this time I will approach the problem in a different way.

Instead of inserting records and populating these with the correct field values, I will instead maintain the number of rows and create a new column “To Date”, so that the new table will become a list of intervals.

Here’s how you do it:

1. Determine which time range you want to work with. The beginning of the range must be before the first date in data and the end of the range must be after the last.
2. Load the source data, but change empty dates to the beginning of the range defined in the previous bullet. The change date should be loaded as “From Date”.
3. Sort the table first according to Currency, then according to the “From Date” descending so that you have the latest dates on top.
4. Run a second pass through data where you calculate the “To Date”. If the current record has a different currency from the previous record, then it is the first record of a new currency (but its last interval), so you should use the end of the range defined in bullet 1. If it is the same Currency, you should take the “From Date” from the previous record, subtract a small amount of time, and use this value as “To Date” in the current record.

In the QlikView script, it could look like this:

Let vBeginTime = Num('1/1/2013');
Let vEndTime = Num(Now());

Tmp_Rates:
LOAD Currency, Rate,
   Date(If(IsNum([Change Date]), [Change Date], $(#vBeginTime))) as FromDate
   FROM Rates ;

Rates:
LOAD Currency, Rate, FromDate,
   Date(If(Currency=Peek(Currency),Peek(FromDate)-0.00000001, $(#vEndTime))) asToDate
   Resident Tmp_Rates
   Order By Currency, FromDate Desc;

Drop Table Tmp_Rates;

When this is done, you will have a table listing the intervals correctly. This table can then be used in a While loop to generate all dates in the intervals (See Creating Reference Dates for Intervals) or with an IntervalMatch to compare with an existing date.

In this example, I subtract 0.00000001 from the date in the previous record. This corresponds to roughly a millisecond. This means that the “To Date” will have a value of one millisecond before midnight, but formatted to show the date only. The reason I do it this way, is for the IntervalMatch to work: No point in time will belong to two intervals.

HIC

Further reading related to this topic:

IntervalMatch

[rwunderlich]

Thanks for the post Henric. I appreciated the idea of subtracting -0.00000001 to close the interval.

I've got a Qlikview Component posted for comment at

http://community.qlik.com/message/314959

that optionally combines the functions of creating the interval from this post with expansion to discrete reference dates as in your other post. I'd appreciate any insights you have to offer.

-Rob

[jagannalla]

Hi,

It is really good idea, Can you share how substring is working here i.e. -0.00000001.

Here what i understood is, it is catching the date value and reducing that date to 1 month and gives previous month date of that date. If I want to reduce 2 or 3 months then which substring i need to use. I know the use of addmonths to reduce 1 month. But I'm happy to see the substring functionality. I want to know how it works?

-Jagan

[hic]

There is no string function used here. Instead, the magic is in the dual format, i.e. that dates have both a numeric representation and a string representation. For instance, Feb 15th, 2013 has the following representation: {41320,'2/15/2013'} i.e. day no 41320 since 1899-12-30 and US date format.

The innermost calculation in the Load statement is "Peek(FromDate)-0.00000001", i.e. an arithmetic subtraction of a very small time unit - approx 1 ms. For arithmetic operations, the numeric value of the dual is always used, so QlikView calculates "41320-0.00000001", which is 41319.99999999. Then the Date() function is used so that we get a proper date format again: {41319.99999999,'2/14/2013'}, i.e. the day before.

Makes sense?

HIC

[jagannalla]

Thanks Henric.

[Anonymous]

Very Help full HC!!

Regards  & Happy New Year

anant

[#7]

Perfect, thank you !

[#8]

Gr8 one

[#9]

Awesomest!! Thanks Mr. Henric, your blog made my life so easier! Thanks again for this solution.

[bc-thebruuu]

Hi Henric

Great post ,Thanks you!

May you please confirm that intervalMatch takes into considerations milliseconds in QV 11.2?

I have an history of actions which go till milliseconds precision and when I create intervals and use IntervalMatch, I end up with intervalls which are related to actions that happen milliseconds later than the end of the interval ... it seems to me that IntervalMatch works ok till seconds precision 'only'...

[hic]

To my knowledge, Intervalmatch works perfectly well also for sub-milliseconds. And has always done so.

However, many of the time and date functions round to milliseconds, so you need to be careful with what you feed intervalmatch. For instance, if MyTime is a field with full resolution (rounding errors in the pikoseconds range) then Time(MyTime,'hh:mm:ss.ffff') will be displayed rounded to milliseconds - no matter how many f:s you put in the format code. It will not round the number, but it will display it rounded.

Other functions will make a "hard" round to the nearest millisecond. I think MakeTime() does this, for instance.

Unfortunately, we cannot change this, since we use third party library functions for many of these functions.

So, make sure that you really have the necessary precision in the input numbers, and use a numeric display, e.g. Num(MyTime) , when you verify the result.

HIC