Qlik Community

Ask a Question

Qlik Design Blog

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

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
Henric_Cronström

Source table.png

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. Source table Peek.pngRun 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;

Interval table.png

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

12 Comments
rwunderlich
MVP & Luminary
MVP & Luminary

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

5,044 Views
jagannalla
Partner
Partner

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

0 Likes
5,044 Views
Henric_Cronström

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

5,044 Views
jagannalla
Partner
Partner

Thanks Henric.

0 Likes
5,044 Views
anantmaxx
Specialist
Specialist

Very Help full HC!!

Regards  & Happy New Year

anant

0 Likes
5,044 Views
Not applicable

Perfect, thank you !

0 Likes
5,044 Views