I have 2 tables with 1 common filed called NTID. Table # 2 has NTID of user and login time. Tables # 1 has NTID and manager id.
Tables # 2 comes from application login tracker and Table # 1 comes from current employee database.
As users join company and leave, Table # 1 has ONLY the current employee records. Tables # 2 as is maintained by the application has history of all the users that are using application and have used application in the past.
I am trying to get this:
Table # 1 Tables # 2
Manager id NTID NTID Login Date
P A A 1/1/2010
P B B 1/1/2010
Q C C 1/1/2010
Q D D 1/1/2010
In this example A,B,C,D and E are active employee where as P and Q are manager of A & B and C, D & E respectively. A, B, C & D are user of the application that is why there is login information for A, B, C & D. E is the odd one out. X, Y & Z are ex-employee who used application in past are not in people database coz they have left the company.
I want to make a bar char with 'Manager id' as dimension and count of distinct (NTID) as expression.
Expected values are:
P = 2
Q = 2 but I get Q = 3.
How do I eliminate the last row? I am loading both the tables # 1 & # 2 as is from the Oracle databases and letting QV do the join.