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
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.
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...];
@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;
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;
add a noconcatenate prior to EmpID:
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)');
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
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?
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