I would like to filter my data source by itself. In SQL it is just INNER JOINNING a table by itself.
FROM table1 INNER JOIN (SELECT id FROM table1 WHERE variable = ‘X’ AND value = 1) q1 ON table1.id = q1.id
WHERE table1.variable = ‘Y’
As you can see I want to present only the variable which equals ‘Y’ with respect to variable =’X’ and value=1.
I can also write it like this,
WHERE variable = ‘Y’ AND id IN (SELECT id FROM table1 WHERE variable = ‘X’ AND value = 1)
I am using a long data file which means my primary key is 'id' and 'variable' together. So, I want all the
variable = ‘Y’ data to be presented only if the 'id' has
variable = ‘X’ AND value = 1. How do I translate this process in Tableau dashboard?
Any suggestions on how to do it without inner joining the data source by itself? I tried the inner join way but my data is very large which resulting in too much processing time and it makes all the other processes extremely slow.
First, just point your data source at table1 without any other changes. Plain and simple.
Second, back on a worksheet, select the id field in the datapane and right click to create a set. Choose the all radio button on the general tab of the set dialog pane, and then switch to the condition tab. Define the set via the formula
max(variable = 'x' and value = 1). Call your set something meaningful like ids_having_an_X1. This will create a set of ids that have at least one data row matching your condition. Think of it as a list of ids that could go inside a SQL IN (...) clause if that helps
Now you can use your set on the filter shelf to only include those ids in the query, or in calculations, or on other shelves.
To get the effect of your where clause, put variable on the filter shelf choosing only the value 'Y'