Forum Discussion
Matching values between 2 tables when using a date slicer
Hello All
Hoping for some assistance on the following
I have 2 tables.
1, Employee
2. Visits
The employee table is simply a list of all employees and the visit table contains all client visits attended by the employee's
What im trying to acheive is the following
When using the date slicer im hoping to show a list of employees from the employee table that have not had a visit within that date range.
Eg , I have for example an employee count of 200 on the employee table and if choose a date range (24/4/23 - 30/4/23) from the visit table , i am hoping to see a list of employees that have visits in that date range and a list of employees that do not
The 2 tables have a relationship from a client ID.
Would appreciate the assistance
8 Replies
Hi,
Share data in a format the can be pasted in an MS Excel file.
Apologies Ashish
Here is a small sample size of data from 2 tablesVisit table.
Client ID Visit Start Date Visit Visit ID Employee ID AC000802186 2023-04-24 1516014 AC001971161 AC003647289 2023-04-24 1609546 AC003697811 AC000865524 2023-04-24 1591665 AC003388602 AC000061390 2023-04-24 1609253 AC000008651 AC001607353 2023-04-24 1442254 AC001432971 AC001570054 2023-04-24 1582205 AC000002261 AC001019508 2023-04-24 1541569 AC001400185 AC000804586 2023-04-24 1540108 AC000002251 AC002512224 2023-04-24 1612147 AC003649687 AC000747854 2023-04-24 1611079 AC000013024 AC001379982 2023-04-24 1610968 AC000002293 AC000808603 2023-04-24 1442003 AC001971080 AC000000398 2023-04-24 1520678 AC002773931 AC001088333 2023-04-24 1443823 AC003697811 AC000000222 2023-04-24 1441325 AC000002279 AC000840309 2023-04-24 1578925 AC001432971 AC003092621 2023-04-24 1449251 AC001936818 AC000998309 2023-04-24 1508647 AC000012964 AC002294527 2023-04-24 1447635 AC000143289 Employee table
Employee ID Has Visit Job Title AC003697811 Yes Community Support Workers AC003649687 Yes Community Support Workers AC003388602 Yes Community Support Workers AC002773931 Yes Community Support Workers AC001971080 Yes Community Support Workers AC001971161 Yes Community Support Workers AC001936818 Yes Community Support Worker AC001432971 Yes Community Support Workers AC001400185 Yes Community Support Workers AC000143289 Yes Community Support Workers AC000012964 Yes Community Support Workers AC000013024 Yes Community Support Workers AC000008651 Yes Community Support Workers AC000002293 Yes Community Support Workers AC000002251 Yes Community Support Workers AC000002239 No Community Support Workers AC000002279 Yes Community Support Workers AC000002261 Yes Community Support Workers AC000002243 No Community Support Workers AC000002231 No Community Support Workers Note: The " Has Visit" column is entered manually and example of what i hope to acheive when slicing from the visit table.
Obviously this would change when selecting the date range and depending on which empoyee has visit for the selected date range
Please let me know if more information is required
Thanks in advanced
Hi,
Your data does not seem reepesentative. You have taken only 1 date - please take more dates as well. Also, within the date range selected, what is an employee visited on a certain date and not on another. In such a scenario, since in the result, the employee ID should appear only once, what should the result be - Yes or No. As requested, please share a logical/resresentative example and show the expected result on that revised sample.
