Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a table of employee IDs and I need to create an entry for each day of the year. What's the best way to do this?
Example:
empID 000001 then I need an entry for each day for this employee.
00001 Jan 1, 2020
00001 Jan 2, 2020 etc.
Thanks
i ran your code using made up emp ID and am able to generate the dates
I get this message:
Table not found error
Table 'EmpID' not found
left join(EmpID)
LOAD date(date('1/1/2020') + iterno()-1) as Date
autogenerate 1
while date(date('1/1/2020') + iterno()-1) <= date('12/8/2020')
Here's my script:
Employees:
LOAD
[Employee Number] as ENo
FROM
[D:\Qlikview\QVD\HR\Infotype0000.QVD] (qvd)
where Employment_STAT2= '3' and [End Date] = '12/31/9999'
;
EmpID:
LOAD
fieldvalue('ENo',recno()) as ENo
autogenerate fieldvaluecount('ENo')
;
left join(EmpID)
LOAD date(date('$(vStartDate)') + iterno()-1) as Date
autogenerate 1
while date(date('$(vStartDate)') + iterno()-1) <= date('$(vEndDate)');
;
left join(Employees)
LOAD *
resident EmpID;
drop table EmpID;
Exit Script;
Ignore my message forgot the Noconcatinate
Thanks Edwin for all the help.
np, considering the dataset is small, i would just go with your solution which makes for a simpler expression -> easier to maintain.