topic capture dates in between start date and end date in QlikView

capture dates in between start date and end date

Sun, 12 Feb 2012 12:50:00 GMT

Hello all,

I tried looking for this but could not find a solution

I have a table with the following table loaded into Qlikview

Load

[Employee Number]

Absence Type

[Date Start]

[Date End]

I now need to recreate the table so that it adds a date column which capture all the dates an employee took a leave

Load

[Employee Number]

Absence Type

Date

Figured I need to loop in order to do this. In programming world the logic would be

load

$vDifference = [Date End] - [Date Start]

[Employee Number]

Absence Type

For i = 0, i < $vDifference, i++

[Date Start] + i as Date;

How do I do this in Qlikview?

capture dates in between start date and end date

swuehl — Sun, 12 Feb 2012 13:02:33 GMT

You can use a while statement in your load to achieve what you want, like

SET DateFormat='DD.MM.YYYY';

INPUT:

Load * INLINE [

Employee Number, Absence Type, Date Start, Date End

100, Sick, 01.01.2012, 10.01.2012

100, Sick, 15.01.2012, 21.01.2012

];

RESULT:

LOAD [Employee Number], [Absence Type],

Date([Date Start]+iterno()-1) as Date

resident INPUT while [Date Start]+IterNo()-1 <=[Date End];

drop table INPUT;

The INPUT table is just to load two sample lines. The important part is the while statement

while [Date Start]+IterNo()-1 <=[Date End];

and the use of the iterno() function in the statement that defines Date field

Date([Date Start]+iterno()-1) as Date

Hope this helps,

Stefan

capture dates in between start date and end date

Tue, 21 Feb 2012 04:25:56 GMT

thanks it worked .

the logic doesnt make sense though, you mind explaining what interno() does ?

capture dates in between start date and end date

Wed, 22 Feb 2012 10:25:33 GMT

I was curius about IterNo() too (The name is not really self explaining).

The reference guide (installs with QV, but in the "Programs/QV" folder) is a good source

(But the examples are rather short and simple. they are often useless for more delicate problems)

reference guide:
"IterNo( )
This function only has a meaning if used together with a while
clause (see the documentation on “Load” on page 317). IterNo( )
returns an integer indicating for which time one single record is
evaluated in a load statement with a while clause. The first iteration
has number 1."

So, IterNo writes in the new collumn "Date". It writes Dates between [Date Start] and [Date End].

It does so, by writing Datens higher as [Date Start] until [Date End] is reached.

Do i need to mention, that the example in the reference guide is simplified too much to be any use for this (or any other problems besides writing some integers). There is an example, but its in the example secion of LOAD (and only to be found by full text search).

capture dates in between start date and end date

swuehl — Wed, 22 Feb 2012 11:33:14 GMT

I think the reference guide is often a bit short on explaining, sometimes unclear or even wrong. In this case, I personally find the explanation sufficient, could be because I do have a programming background so I may implicitely add information that others may miss here.

You need to look first at the while statement (from the Help):

while is a clause used for stating whether a record should be repeatedly read. The same record is read as long as criterion is true. In order to be useful, a while clause must typically include the IterNo( ) function.

You need to make yourself clear that there is an input table (coming from your source, in this example it's the INLINE table) and the output table (going into the QV data model). Your load is transforming this input table into an output table, in simple settings, it's just piping the exact same number of records, fields through, like a LOAD * from Table;

The while clause will create multiple output records from one input record, using the while condition to decide when to stop. And the only thing that changes between creating two output records from one input record is the iteration no, which you get by calling the iterno() function. In e.g. C programming language, you would create a counter variable and explicitely increment this variable in each loop.

So, this should explain

while [Date Start]+IterNo()-1 <=[Date End];

--> Create one output record for each date between Date Start and Date End (The calculation is possible since QV dates have a numerical representation, the integer part is counting day since a fixed starting point in time, like Excel or other systems do also, for example 01.01.2012 has a numerical representation of 40909, 10.01.2012 of 40918). I need to subtract 1 since iterno() starts with 1 for the first created output record, but I want to get at least one record if Date Start and Date End are the same.

If this is all I do, I would create just duplicate output records created from my input record. So I can use the iterno() another time to actually change each output record a bit

Date([Date Start]+iterno()-1) as Date

Create a new Date value, starting from the Date Start read from the input table, adding one day per loop increment (again, I need to take care of the iterno() starting from 1 since I want also the Date Start to be included).

If you compare the two lines of my input INLINE table with the output table after the executed load, it should make everything clear.

Hope this helps,

Stefan

Re: capture dates in between start date and end date

userid128223 — Sun, 11 Sep 2016 03:53:21 GMT

This does not seem to work as intended. It works fine it you are working with 2 dates from same month. However when it dates span on multiple months. Dates creation goes from 1-100

so if dates are from 20160701 to 20160901

It will create

20160701......20160799

20160801......20160899 .... 20160901

how can you limit the script to get the counts for number of days in that month.

Here is what i tried:

// Define Stop date

vStopDate=date(Today()-1,'YYYYMMDD');

INPUT:

Load * INLINE [

StartDate

20160801

];

// Create list of date from Start date to Stop date. It will create all the dates in between

DateList:

LOAD

//Date([Date Start]+iterno()-1) as Date

Date#([StartDate]+iterno()-1,'YYYYMMDD') as DatesArray

resident INPUT while [StartDate]+IterNo()+1 <= $(vStopDate);

Results:

20160801 ...........20160899,20160800,20160901........20160909

Re: capture dates in between start date and end date

swuehl — Sun, 11 Sep 2016 08:20:08 GMT

Your input table record values for StartDate are not correctly read in as dates

Get the Dates Right

Try

LET vStopDate=Today()-1;

INPUT:

Load Date#([StartDate],'YYYYMMDD') as StartDate INLINE [

StartDate

20160801

];

// Create list of date from Start date to Stop date. It will create all the dates in between

DateList:

LOAD

//Date([Date Start]+iterno()-1) as Date

Date([StartDate]+iterno()-1,'YYYYMMDD') as DatesArray

resident INPUT while [StartDate]+IterNo()+1 <= $(vStopDate);

Re: capture dates in between start date and end date

userid128223 — Sun, 11 Sep 2016 15:42:25 GMT

Thanks works well but something is still not right

LET vStopDate=Today()-1; // Should evaluate to Sep-10

however it only calculates data up to Sep-08. Any idea why that would happen.

Re: capture dates in between start date and end date

sunny_talwar — Sun, 11 Sep 2016 16:04:15 GMT

Try this:

LET vStopDate=Today(1)-1;

Today() is usually the date associated with last time you saved your application. Today(1) is true today

Re: capture dates in between start date and end date

userid128223 — Sun, 11 Sep 2016 16:18:31 GMT

Thanks

Tried your suggestion, did not work.

Re: capture dates in between start date and end date

swuehl — Sun, 11 Sep 2016 17:23:52 GMT

In addition to use Today(1), you may want to change the WHILE clause to

DateList:

LOAD

//Date([Date Start]+iterno()-1) as Date

Date([StartDate]+iterno()-1,'YYYYMMDD') as DatesArray

resident INPUT while [StartDate]+IterNo()-1 <= $(vStopDate);

Re: capture dates in between start date and end date

Anonymous — Tue, 09 Jan 2018 09:23:15 GMT

Thank you very much... u made my day. I spent 1 day on searching this... Thanks again

Re: capture dates in between start date and end date

ltc — Fri, 26 Jun 2020 17:14:25 GMT

Hi Stefan,

This works pretty great, except it the While/IterNo logic is only being applied to my samples lines from the Input table. How can I get the logic to be applied to all of the lines in my data set?

Thanks!

Re: capture dates in between start date and end date

ltc — Sat, 27 Jun 2020 03:13:38 GMT

Hi Stefan,

I made a separate post for my question with sample data. If you could take a look, I would greatly appreciate it.

Thanks!

https://community.qlik.com/t5/Qlik-Sense-Advanced-Authoring/Need-help-with-While-InterNo/m-p/1722712#M1453