topic Re: Add Date to Every EmpID in QlikView

Add Date to Every EmpID

tmumaw — Fri, 15 Nov 2024 23:29:40 GMT

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

Re: Add Date to Every EmpID

edwin — Mon, 07 Dec 2020 22:12:12 GMT

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.

Re: Add Date to Every EmpID

edwin — Mon, 07 Dec 2020 22:15:19 GMT

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

Re: Add Date to Every EmpID

Kushal_Chawda — Mon, 07 Dec 2020 22:36:40 GMT

@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;

Re: Add Date to Every EmpID

tmumaw — Tue, 08 Dec 2020 12:50:54 GMT

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;

Re: Add Date to Every EmpID

edwin — Tue, 08 Dec 2020 13:53:58 GMT

add a noconcatenate prior to EmpID:

Re: Add Date to Every EmpID

edwin — Tue, 08 Dec 2020 14:09:26 GMT

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)');

Re: Add Date to Every EmpID

tmumaw — Tue, 08 Dec 2020 14:09:27 GMT

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

Re: Add Date to Every EmpID

edwin — Tue, 08 Dec 2020 14:12:57 GMT

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?

Re: Add Date to Every EmpID

tmumaw — Tue, 08 Dec 2020 14:24:21 GMT

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

Re: Add Date to Every EmpID

edwin — Tue, 08 Dec 2020 15:51:37 GMT

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

Re: Add Date to Every EmpID

tmumaw — Tue, 08 Dec 2020 16:25:48 GMT

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;

Re: Add Date to Every EmpID

tmumaw — Tue, 08 Dec 2020 16:28:19 GMT

Ignore my message forgot the Noconcatinate

Re: Add Date to Every EmpID

tmumaw — Tue, 08 Dec 2020 16:41:42 GMT

Thanks Edwin for all the help.

Re: Add Date to Every EmpID

edwin — Tue, 08 Dec 2020 16:58:52 GMT

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