Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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...
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
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
Thank you Mark,
I try your script, it seems to be good.
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