topic Date in qlikView scripting in QlikView

Date in qlikView scripting

Thu, 14 May 2009 03:39:24 GMT

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

Date in qlikView scripting

Thu, 14 May 2009 03:56:07 GMT

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

Date in qlikView scripting

Thu, 14 May 2009 14:22:08 GMT

Thank you Mark,

I try your script, it seems to be good.

Date in qlikView scripting

rwunderlich — Thu, 14 May 2009 14:28:49 GMT

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