Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
tmumaw
Specialist II
Specialist II

Issue with Joins

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

Labels (1)
1 Solution

Accepted Solutions
edwin
Specialist III
Specialist III

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

View solution in original post

6 Replies
edwin
Specialist III
Specialist III

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?

edwin
Specialist III
Specialist III

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

edwin
Specialist III
Specialist III

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

tmumaw
Specialist II
Specialist II
Author

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.

edwin
Specialist III
Specialist III

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

View solution in original post

tmumaw
Specialist II
Specialist II
Author

Yep....Thanks for your help again.