Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Date in qlikView scripting

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

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

3 Replies
Not applicable
Author

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

Not applicable
Author

Thank you Mark,

I try your script, it seems to be good.

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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