Add Date to Every EmpID

[tmumaw]

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

[edwin · Accepted solution]

i ran your code using made up emp ID and am able to generate the dates

[tmumaw]

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;

[tmumaw]

Ignore my message forgot the Noconcatinate

[tmumaw]

Thanks Edwin for all the help.

[edwin]

np, considering the dataset is small, i would just go with your solution which makes for a simpler expression -> easier to maintain.