topic Re: Issue with Joins in QlikView

Issue with Joins

tmumaw — Sat, 16 Nov 2024 17:43:26 GMT

Good morning everyone,

Got a question. I have a table with all my employees by day in it, I also have another table with employees time. In the employee's time table I might have only 10 entries for an office which has 13 employees. All 13 employees are in my employee by day. I'm trying to join the 2 so I can see all employees with and without time. Any ideas? Here is my script and tables. What I would like to see is list of employees by day ones with time and ones without time.

Thanks

all my employees:
LOAD
ENo,
"Date",
EmpKey
FROM [lib://QVD/HR/EmpDaily.QVD] (qvd)
;

employees time:
Load
Pers.No._PERNR.CATSDB & Num(Date_WORKDATE.CATSDB) as EmpKey,
Pers.No._PERNR.CATSDB as ENo,
Right("Send. CCtr_SKOSTL.CATSDB",6) as [Profit Center],
Date_WORKDATE.CATSDB,
Num(Date_WORKDATE.CATSDB) as NumTimeDate,
Hours_CATSHOURS.CATSDB,
"A/A type_AWART.CATSDB",
1 as HasTime
FROM [lib://QVD/Billing/CATS/CATSDB.QVD]
(qvd)
where "A/A type_AWART.CATSDB" ='0099' or
"A/A type_AWART.CATSDB" ='0069' or
"A/A type_AWART.CATSDB" ='0068'
;

Re: Issue with Joins

edwin — Wed, 09 Dec 2020 19:01:44 GMT

you are obviously joining by Eno and EmpKey and it looks like a simple join (guessing employees table contains all the manufactured dates or is this a different project?)

what are you observing though? are they not joining properly? are you sure the Eno/EmpKey value combination is present in both tables?

Re: Issue with Joins

edwin — Wed, 09 Dec 2020 19:03:01 GMT

shouldnt your join just be Eno and Date? obviously, employees time table has date time but you should get just the date component and use that in the join

Re: Issue with Joins

edwin — Wed, 09 Dec 2020 19:53:00 GMT

take a look at this and hope it gives you an idea

Re: Issue with Joins

tmumaw — Wed, 09 Dec 2020 20:05:43 GMT

Thanks Edwin for all your help. This is pretty close to what I ended up doing. I created a concatenated key made up of empno and a numeric date on both tables and did a left join on the time table. Then read it in as a resident and where the length of hours worked was greater than zero I used hours worked otherwise I made it zero.

Re: Issue with Joins

edwin — Wed, 09 Dec 2020 20:35:16 GMT

and you just needed to know what days there are missing entries rt?

Re: Issue with Joins

tmumaw — Wed, 09 Dec 2020 20:43:53 GMT

Yep....Thanks for your help again.