Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
thanks it worked .
the logic doesnt make sense though, you mind explaining what interno() does ?
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).
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
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
Your input table record values for StartDate are not correctly read in as dates
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);
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.
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
Thanks
Tried your suggestion, did not work.