Extract Distinct List of Users to Group for Lookup Reference

[JPSeagull]

Hi. I was asked to see if any of our Reviewers are doing the Schedulers' job. Reviewers can schedule appointments, but Schedulers cannot Review and sign off on the appointment. 

So, I would like to generate a Flag where if anyone who is a Reviewer by their role identified under "Reviewer ID" is also showing up under the "Scheduler ID." I can do a simple TRUE/FALSE flag in Tableau, but I realized that often a Reviewer will schedule the appointment, but not necessarily close their own appointment. So I need to just have a way to always identify the Reviewers (without manually entering IDs whenever we have a change) as one group and see if they show up as a Scheduler not matter who actually Reviews the appointment. I am attaching a mock up of fake names, and User IDs. 

The results I would like to get is:

Num  Den
30      50 

Of the 50 appointments, 30 of them were generated/scheduled by someone who is actually a Reviewer by evidence of them being at least showing once in the Reviewer ID and Name column. Again, Schedulers will never show under the Reviewer ID and Name column. I highlighted the Reviewers green under the Scheduler column to make it easy to see them

[Qiu]

@JPSeagull 
Maybe something like this?

[mceleavey]

Hi @JPSeagull ,

Ok, I think I understand.

I've attached the workflow. This is simply a case of getting a unique list of Reviewers and joining them back to the main data using the link of Reviewer ID to Scheduler ID. This creates a subset of those records where the scheduler is also a reviewer at least once.

Then count all records.

Hope this helps,

M.

[mceleavey]

@Qiu , over to you, my friend. 

Too late for me to be doing solutions!

[Qiu]

@mceleavey 
I believe it is your social time now.😁
Got your PM, will act on it. Thanks a lot!

[JPSeagull]

Thank you, Qiu! This gets me much closer. I neglected to share a screen shot of my workflow, but I have something similar. Although I indicated I needed to generate the counts, I also need to show a "flag" column, by row of each appointment scheduled by Reviewer or not. Thank you again.

[JPSeagull]

@mceleavey Thank you for this. 

This gets me much closer. I neglected to share a screen shot of my workflow, but I have something similar. Although I indicated I needed to generate the counts, I also need to show a "flag" column, by row of each appointment scheduled by Reviewer or not. I see how you generated the Count which is what I needed to do, but I have to show the flag.....I apologize for not adding that detail. Ultimately, I need to bring in another column showing by row if the appointment maker was a Reviewer so I can bring it in to Tableau to do the counts as part of larger dashboard project.  Thanks again!

[mceleavey]

Hi @JPSeagull ,

I just added it for you.

M.

[atcodedog05 · Accepted solution]

Hi @JPSeagull 

Here is the updated solution. Retains the data order.

Hope this helps : )

[JPSeagull]

@atcodedog05 Thank you! It's perfect.