Date in qlikView scripting

[#1]

Hy,

I want to know if it's possible to find all the day between 2 dates :

For example ==> in a table Salary number = 001, Date of Entry = 01/01/2009, Date of exit = 04/01/2009

I want to create a new table ==> 001, 01/012009, 02/01/2009, 03/01/2009,04/01/2009

Thanks a lot and have fun with Qlik...

[#2 · Accepted solution]

You can loop your salary table and add rows to a date table for each record. Like this:

FOR RecordNummer = 1 TO (NoOfRows('Salary'))
 LET vSalaryNum = Trim(FieldValue('Num', '$(RecordNummer)'));
 LET vSalaryEntryDate = Trim(FieldValue('Date_Entry', '$(RecordNummer)'));
 LET vSalaryExitDate = Trim(FieldValue('Date_Exit', '$(RecordNummer)'));

 SalaryDates:
 LOAD
 '$(vSalaryNum)' as Salary_Number,
 Date($(vSalaryEntryDate) - 1 + IterNo()) as Date
 AUTOGENERATE 1
 WHILE Date($(vSalaryEntryDate) + IterNo()) <= Date($(vSalaryExitDate));
NEXT

It's not exactly what you are requesting but I maybe also a solution. Thanks for the fun with Qlik.. 🙂

regards Mark

[#3]

Thank you Mark,

I try your script, it seems to be good.

[rwunderlich]

You can also use IntervalMatch to link the ranges into a date table. See the QV Cookbook example "Count days in a transaction using IntervalMatch" for an example of how to do this. You can download the QV Cookbook from
http://robwunderlich.com/Download.html

-Rob