I have table 'tickets' that contains info about support tickets and, specifically, which user they are assigned to (a user ID).
I have a table 'users' that contains info about users, including their user ID, and also a field denoting their specific department (HR, IT, etc.).
My goal is to be able to filter tickets based upon which department they are (and are NOT) assigned to. The issue I'm running into is, there are some tickets that get assigned to user ID's that do not exist in my 'users' table (generic system accounts, automated accounts, etc.). When I attempt to filter my tickets by department (let's say IT), it does indeed show me all tickets that are assigned to IT users; however, attempting to filter by tickets NOT assigned to IT results in a bit of a number gap (i.e. I have 100,000 tickets total; filtering for IT shows 25,000; filtering for NOT IT shows 30,000; the remaining 45,000 tickets are assigned to user IDs that do not exist in my 'users' table).
Example data:
'tickets'
ticket number user_ID
1 A
2 B
3 A
4 D
5 E
'users'
user_ID department
A IT
B HR
C IT
Filtering for tickets which have user_IDs NOT belonging to the IT department, it misses all those tickets who have user_IDs that do not exist in the users table (tickets 4 & 5, since these records essentially do not have a department field). I would like to include these tickets when searching for those NOT in IT.
Currently I have 2 tabs with 1 load statement each:
tickets tab:
LOAD
ticket_ID,
user_ID
FROM
..\DATA\tickets.qvd
(qvd);
users tab:
LOAD
user_ID,
department
FROM
..\DATA\users.qvd
(qvd);
I suppose I'm looking for a way to check if the 'tickets' user_ID exists in the 'users' table, and set a flag if it does / does not. Adding the user_IDs manually to the 'users' table is not an option.
Thanks
