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]

iteration would be a good start:

date(date('1/1/2020')+iterno()-1) as Date...
while iterno()<= SomeNumber

but i would ask what the purpose is.  if you already have a million rows iterating through them would multiple it by 365 for 1 year.  depending on the case, maybe leveraging the associative power of QV would work (like say building a link or bridge to the calendar).

so it depends on what you want to do with it.

[edwin]

i love using iterno() when building test data:

load Plant, date(today()-iterno()+1) as Date, floor(rand()*1000) as Amount
while iterno()<=365;
load * inline [
Plant
P1
P2...];

[Kushal_Chawda]

@tmumaw  try below. You can change the start and end date variable according to your need.

// Your actual data
Data:
load *,
     Emp_ID
FROM table;

let vStartDate = makedate(2020,1,1);
let vEndDate = today();

EmpID:
LOAD fieldvalue('Emp_ID',recno()) as Emp_ID
autgenerate fieldvaluecount('Emp_ID');

left join(EmpID)
LOAD $(vStartDate) + iterno()-1 as Date
autogenerate 1
while $(vStartDate) + iterno()-1 <= $(vEndDate);

left join(Data)
LOAD *
resident Emp_ID;

drop table Emp_ID;

[tmumaw]

I get the message table EmpID not found.  Am I missing something?

Employees:
LOAD
"Employee Number" as ENo
FROM [lib://QVD/HR/Infotype0000.QVD] (qvd)
where Employment_STAT2= 3 and "End Date" = '12/31/9999'
;

let vStartDate = makedate(2020,1,1)
;
let vEndDate = today()
;

EmpID:
LOAD
fieldvalue('ENo',recno()) as ENo
autogenerate fieldvaluecount('ENo')
;

left join(EmpID)
LOAD $(vStartDate) + iterno()-1 as Date
autogenerate 1
while $(vStartDate) + iterno()-1 <= $(vEndDate)
;

left join(Employees)
LOAD *
resident EmpID;

drop table EmpID;

Exit Script;

[edwin]

add a noconcatenate prior to EmpID:

[edwin]

also, your variables are text once evaluated as $(variable).  so adding a number to them results in just the added number, adjust your code  a little:

LOAD date(date('$(vStartDate)') + iterno()-1) as Date
autogenerate 1
while date(date('$(vStartDate)') + iterno()-1) <= date('$(vEndDate)');

[tmumaw]

Edwin suggested adding Noconcatenate before EmpID which worked, however I'm only getting 1 record for each employee.  What I need is empid and a entry for every date between the 2 variables.  

Example:

000001     01/01/2020

000001     01/02/2020

etc.....

Thanks

[edwin]

as i said earlier, you actually multiplied your records by 365.  if you have 1M recs it will balloon to 365M.  what is the motivation to create dates for each emp?

[tmumaw]

We have a time entry model that we need to identify employees which have not entered time by day.  For example I have 13 employees in a profit center and only 10 of them have entered time on 01/01/2020.  The next day 9 people have entered time.  I need to identify by day who has yet to enter time.  I thought by adding a day for each employee and comparing it to the time entry table would be the easiest way.  I'm open for other ideas.  Thanks