Any chance that you could put together a small sample data set for us to work with and try to make this work?
Unfortunately, I don't see any way to do all of this within a single Tableau join, so you'll have to use a combination of joins, blends, and calculated fields to get there.
Start by creating one data source, which I'm calling Users as follows:
Create another called Pets. Note: You didn't include a pets sample file, so I just mocked one up.
Finally, one called Roles.
Now go to a new sheet and and create a simple view like this as a starting point:
Next, click on the Pets and Roles data sources and set User ID as the linking field for each:
In the Roles data source, create a calculated field that will count how many instances where the following criteria are met: roles.resource_type = 'Business' OR roles.name = 'professional'
// If resource type is Business or role name is professional then 1 else 0.
IF [Resource Type]="Business" OR [Name]="professional" THEN
Now go back to your view and add in SUM(Number of Values) from the Pets data source and SUM(Exclude Count) from the Roles data source.
Number of Records here becomes your Pet Count (You can rename it in the Pets data source if you like). Exclude Count will give you a zero if the user should be included and a value >=1 if it should be excluded. So, let's drag Exclude Count up to filters and set it to only include values of 0.
Once this is done, I think you should pretty much have the same results as the SQL query. It isn't pretty, but it should work.
Of course, another thought would be to do some data prep work, perhaps with Tableau Prep, to get this data into the simple format you're looking for. Ultimately, I'd probably recommend this, if you have tools available to you.
I'm attaching my workbook. If this addresses your question, please be so kind as to mark my response as the "correct answer" so we can close this thread and so that others can quickly find the answer if they have similar questions in the future. Thanks!
Blend.twbx 549.9 KB
Tks a lot for your help!
I think we are on the right path, but still I wasn't able to match the CUSTOM SQL record numbers with this one.
Using this method I got 6526 records, the CUSTOM SQL has 6546.
I figured that I have a lot of records in NAME on Roles with Null, so I included those results.
Using data prep it would be easier?
It would definitely be easier with a tool such as Tableau Prep.
Do you have access to Tableau Prep and are you familiar with it?
I'm trying to run the CNT on ID, but isn't showing results.
I'm not following you. Where are you doing this? Can you be more specific?
I did what you recommended and that worked pretty well! Now I need to get some more data out of it!
Im trying to CNTD all the ID's to see how many users we have, but isn't working.
I do have access to Tableau Data Prep!
Can you show me a screenshot or an example of the problem? It's difficult to troubleshoot this without it, unfortunately.
Sorry, I'm not sure I can troubleshoot from this. Any chance of seeing a packaged workbook?
I do have access to it.
I’m not an expert, but I can manage it.
Sent from my iPhone