Qlik Community

Qlik Design Blog

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

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
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

2,131 Views
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
2,131 Views

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

2,131 Views
Partner
Partner

Thanks Henric.

0 Likes
2,131 Views
anantmaxx
Valued Contributor

Very Help full HC!!

Regards  & Happy New Year

anant

0 Likes
2,131 Views
Not applicable

Perfect, thank you !

0 Likes
2,131 Views
Not applicable

Gr8 one

0 Likes
2,131 Views
Not applicable

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

0 Likes
2,131 Views
bcavestro
Contributor

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'...

0 Likes
2,131 Views

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

2,131 Views
mayuranp
Contributor

I have been generating the date range from single date in sql. Now can use your script to do that in QVW.

Thanks a lot.

0 Likes
2,131 Views
rajinikanth
New Contributor

Hi Henric,

I have Data as below, When I select any two dates, I need how many ids has the target date got changed, I was trying with the above approach and unable to achieve.( May be i'm not able to do it).

Can you please suggest the way to do this. I need another Date field as CLOSED DATE for my date range selection.

Say if I select DATE_UNIT = '3/Apr/2018' and CLOSED DATE = '2/Jul/2018' I need count as '1' as the target_date got changed from 31/Jul/2018 to 31/Aug/2018.

Similarly for DATE_UNIT = '1/May/2018' and CLOSED DATE = '1/Jun/2018' I need count as '1' as the target_date got changed from 31/Jul/2018 to 31/Aug/2018.

Same for 1/May/2018 to 2/Jul/2018

For 3/Apr/2018 to 1/May/2018 the count of ID should be '0' as the target date didn't got changed.

   

IDDATE_UNITTarget_Date
I-000439593/Apr/201831/Jul/2018
I-000439591/May/201831/Jul/2018
I-000439591/Jun/201831/Aug/2018
I-000439592/Jul/2018

31/Aug/2018

0 Likes
2,131 Views