There are various ways to tackle this kind of problem. Which one is best depends on a few things, including how large the dataset supporting query2 is and how static the reference data is.
One way of doing it is to have 2 sheets, one for query1 and one for query2 and define a filter action on the query2 sheet which limits query1 to the selected value(s). Those sheets might be on the same dashboard, or might just be separate sheets.
I explored a few ways of tackling what was essentially a similar problem in some articles I wrote on Robert Mundigl's Clearly and Simply site, here. I then adapted one of them in this viz of New Zealand Earthquakes.
In both of those cases, the equivalent of your query1 was finding all locations within a certain radius of a given location which was selected by the equivalent of your query2.
Hi Richard, Thank you very much thats an intersting learning from your experience.
As per your statment, if my lookup(query 2 output) is static i can use a excel or another sheet to filter out the criteria at the data engine.(by including list of values in where clause).
But Query2(lookup) values changes daily.
The output of query1 is so huge approx 1tb. So there is no point in running that without any criteria.
So i believe there should be some method that filter of data should be done at data engine rather than tablue.
All that i want to stop tableau from trying to fetch the data before blending. As per your blog i understand that this a present limitation that need to fixed.
Can we do this,
1. fetch the data from query1 for any date(my filter criteria) intially.
2. From then on apply the filter criteria?
Yes, I have taken that approach sometimes when I don't want the query to be executed unconstrained. You can even put a completely different filter dummy condition which returns zero rows initially until you have applied an action filter, then simply remove the dummy filter. You might also be able to achieve the same thing by turning off refresh on the sheet for query1 until you have applied your filter action. I think that works - but I'd have to try it to make sure I'm not missing something.
I'm sure Joe or someone will chip in if there are better ways to achieve what you are trying to do.
I definitely think there is a need for a "lookup filter" to do what you want, though. Maybe version 7. Or 8. ;-)
Yes, I understand that we can do that if we are doing in multiple sheets. My aim is to achieve this on a single sheet.
Thank you once again Richard.
A single view, or is a dashboard OK?
I'm fairly sure I've managed to do this on a dashboard in the way we are discussing - by using one or other of those tricks to avoid executing the big query unconstrained.
The only way I've managed to do it in a single view is via that trick with embedding a list of values in a parameter that I talked about on Clearly and Simply - but that only works with static data - and doesn't really scale all that well, either.
Why does it need to be a single sheet and not a dashboard?
Richards suggestion of using a dashboard with a filter action sounds like the best option to me, with the clearing action set to exclude all values, and likely some other settings.
The Filter action can work across data sources when you specify the fields to use, and the action filter is sent to the data source filtering at the same time as other normal filters.
I mean a single Worksheet not a Dashbboard. I dont want to implement this in a dashboard because i am not going to publish them my main aim is to create the data connections and share the data connections. So that appropriate people will use the attributes/dimension/measures and make themself the useful graphs in way they want... Still we are process of evaluation of this approach so i am trying myself to deal with these scenarios.
This project is very early phase....
Yes, With this approach i would like to filter criteria of query1 with values selected in query2.
Unless I'm missing something, you can't achieve the filtering you are after with data blending, which is what that knowledge-base article covers.
Since your aim is to create the data connections and share the data connections, and your main data that changes daily is in Teradata a table about 1tb, are using custom SQL, and your Excel file is small and static, I would recommend talking with your DBA about incorporating this into your Teradata Data Warehouse to make the distribution of the .tds (a Tableau Data Connection) or .tbm (a Tableau bookmark) simple and improve performance.
My reference data and actual data is also in teradata.
Infact my query1 has 6 tables t1,t2,t3,t4,t5,t6. - T1 is table which has my date, but i dont want to query this directly because of this join it would take very long time.
So i have added another data connect Which would access my only T1 - and fetch the distinct of dates from T1. - call it query 2
And moreover there is no static data. Every day one unique new date would be added to the Query2 List. and there is a chance one date would be deleted too.
So all that i am trying to achieve is Get dates from query2, select the date and with that date i am trying to fetch query1.
But now i understand that may not be possible with out following the Richard's method.
Thank you Everyone.
I agree with Joe Mako - to get from the database(s) exact data that you need to work with in Tableau - you could use query2 as a sub-query RIGHT JOIN'ed to query1