You will indeed need to do this through custom SQL or moving the SQL up to the database as a view.
Unfortunately, the Tableau GUI for joins does not support this level of customization, yet.
Basically, what is happening is one (or more) of the joined in tables have more than one record per join condition.
When this happens, the data is multiplied, as you are seeing.
In the SQL side, you can fix this by joining subqueries with DISTINCT on the values you need (or MAX(), if there are multiple values and you only need one)
Hey Derrick Austin,
Can u help me in reviewing my custom sql what i have created?
if i use custom sql does it effect my performance?
now we have 670 users it will increase upto 5 million users? then data will increase that time does the custom sql will handle the load ? does it effect the performance?
Writing Custom SQL will help you to solve Desiging the Data Model.
After you connected to the Dtabase, "Custom SQL" will appear let side Under list if Tables.
Drag that Custom SQL then develop code as per your Business Need.
If you need the exact SQL query, the following things are required.
1) List of the Tables.
2) Business Requirements with detailed Information.
Best options is create a View at Database level using the SQL Query which is equal to Custom SQL, it makes your job easier.
Thanks for your reply. I already tried with custom sql. My concern here is custom sql will also lead to some performance issues right? how to avoid them when we use a custom SQL?
This is a terrible structure from a database design standpoint. If these are all measurements for a patient, blood pressure and BMI should have been columns in one table as opposed to being separate tables. Query needs to have a key to align the measurements. E.g. how is BMI measurement in the first row of BMI table, related to the blood pressure reading on any given row of blood pressure table. If you have some other identifier e.g. if each of these tables have a date of measurement field, you can join those tables to each other using userID and date as the two join fields and then join that to user table using the userid.
e.g. user - blood pressure - BMI ....
Hi Bora Beran,
Thanks for your reply. yes you are right they are separate tables and date filed is different for the two tables.
for example user is having health score on a specified date he is not having BMi, or blood work on the same date. so this problem is happening i guess.
can i send you the custom query which is written by me. so that you can review that how we can do that better?
There has to be a way to align them. Is there some sort of ordering, 1st measurement, 2nd measurement.. or do they at least align at week level, month level even if it is not the same day?
No . directly we are taking the data from the rest api and we are using it. nothing was done
How would you be able to relate/chart/compare a BMI result with blood pressure if there are multiple results for each user?
If I were to plot BMI against blood pressure is it X-Q, Y-R or X-R, Q-Y?
As of I known, Materialized views improves performance and which reduce time consumtion of Tableau Report.
we need to have a another common filed between them to take a decsison
Can you attach the Custom SQL file as a file to this thread? We can review.
You shouldn't have any major performance issues from it, as long as it is tuned correctly.
does my visual join and this custom sql will gave the same results. that also i need to know.
Your visual join is not doing any unions, just inner/left joins.
This symbol means "left join":
And this symbol means "inner join":
It sounds like some of your tables should be joined differently, to prevent the data multiplication.
Something like this:
FROM mainTable m
JOIN (SELECT DISTINCT EmployeeId, EmployeeName FROM employeeTable) e
ON (M.employeeId = e.EmployeeId)