Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need to perform the following count:
I have 2 tables, Work Requests and EngineerAssignments, these are joined via the work request code field.
I need to know how many work request have an engineer assigned and how many are unassigned (work request may have more than one engineer assigned!)
I know I have 3209 work requests (I can work this out in a text object quiet easlily).
I can't count the number of records in the EngineerAssigments table as its doesn't give a unique count against work request number.
I have 1512 records in the EngineerAssigments table, but as some have multiple engineers are assigned to a single work request this is not the number I need.
The total row in the EngineerAssigments straight table shows 899 - so I assume this is 899 work request which have one or more engineers assigned. this is the value I need to produce in a text object or even to show a pie chart of assigned and unassigned work requests.
for example:
Work Request Table:
Work request Code |
---|
001 |
002 |
003 |
004 |
005 |
006 |
007 |
EngineersAssigned Table:
Work Request Code | Engineer |
---|---|
001 | A |
001 | B |
002 | A |
004 | B |
004 | C |
006 | A |
So here I know there are 7 work requests. And although there are 6 entries in the EngineersAssigned table, there are only 4 work requests which have someone assigned.
So the result I'm looking for would be 4 Assigned Work Requests and 3 Unassigned Work Requests.
Any ideas on the expression i need to count the assigned and unassigned work requests?
Assigned: count({<Engineer={'*'}>} distinct [Work Request Code])
Unassigned: count({$-<Engineer={'*'}>} distinct [Work Request Code])
Assigned: count({<Engineer={'*'}>} distinct [Work Request Code])
Unassigned: count({$-<Engineer={'*'}>} distinct [Work Request Code])
Thanks Gysbert work perfectly