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