Hey Jeff -
This is sort of a chicken-or-the-egg scenario. The answer is "yes", but the grouping itself needs to exist already -- or you need to create it yourself.
How do you know which 12K materials you need to select? Is there something in the data that you can key on to create the group? Otherwise, you'll need to manually create the grouping yourself, which won't be fun if you're dealing with 12,000 entities....
I have my list of 12,000 materials on an excel sheet. Would i need to add that as a secondary data source and create a calculated field on the Primary Data source to be able to group these 12,000 materials? Then go to extract and utilize that calculated field to extract that group?
Yup! That would totally work. Good thinking!
I connected to a secondary data source (excel) with my list of 12,000 materials. I then created a calculated field within my primary data source to equal the Material number on the secondary data source. The field is calculated at an aggregate level due to it being a secondary data source. The issue is that when i go to extract the data that calculated field is not a field that i can select to filter on. Any help?
Just tried this myself and blending and can’t get it to work. While I can create a filter which makes “other than 12K” values go away on a viz, you can’t use the same thing as a filter on the extract correctly.
I also tried using sets as a way to do this – no joy there, either. Where are the 800K rows coming from? Excel as well? If so, you can write custom sql which does a JOIN against the two worksheets and brings in ONLY 12K rows that appear in both sheets…
The thing is i am pulling through a SAP Hana connection to sales data for all 800,000 materials for the past 2 years. The tableau report is taking a little while to filter and wanted to see if I could just see the sales data for those 12,000 materials instead of the 800,000. Hope this answers your question.
Here are two ideas:
1. Create a federated data source and then use a custom query/Tableau Multiple Tables Connection/Custom SQL to join the two together. Most databases either support a) ODBC connections as an output and/or b) linking to other tables from other data sources to create a "linked table" or "linked database". So your Excel spreadsheet might be able to get another tab that would be linking to the Hana connection and then query off the spreadsheet, or SAP Hana would have a linked table based on the Excel spreadsheet, and the Tableau connection would incorporate both.
2. Use Tableau's ad hoc Groups. You could use the Excel data to create a primary group in your sales data using the following instructions: Creating Primary Groups from a Secondary Source Using Data Blending | Tableau Software. Then that ad hoc Group is a dimension that can be used as an extract filter. This has advantage of not requiring work in the data source, the disadvantage is that it has to be manually maintained.
The key stumbling block in the earlier attempts is that the query to generate the extract happens well before data blending, and even then extract filters are based on dimensions, not aggregate measures. What I mean is that calculated fields that use blended data are always aggregates (as you found out), and even though Tableau lets us include dimensions from the secondary in the view, they aren't present at the time the extract is generated, they are only blended in after queries are issued to all data sources.