7 Replies Latest reply on Dec 19, 2013 12:03 PM by Jonathan Drummey

    Filter Extract by Multiple Material Number Dimension

    Jeff Lefeld

      Hello,

       

      I would like to filter my extract to only 12,000 materials out of 800,000 from my datasource. When i select Extract Data and Material Number it brings up the attached box below. Is there a way to inlist a group of materials instead of selecting each of them? My material numbers dont have a set way they were created so i wouldnt be able to use wildcard to capture them all.

       

      Thanks,

       

      Jeff

       

      Tableau Extract.png

        • 1. Re: Filter Extract by Multiple Material Number Dimension
          Russell Christopher

          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....

          • 2. Re: Filter Extract by Multiple Material Number Dimension
            Jeff Lefeld

            Hi Russell,

             

            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?

             

            Thanks,

            • 3. Re: Filter Extract by Multiple Material Number Dimension
              Russell Christopher

              Yup! That would totally work. Good thinking!

              • 4. Re: Filter Extract by Multiple Material Number Dimension
                Jeff Lefeld

                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? 

                • 5. Re: Filter Extract by Multiple Material Number Dimension
                  Russell Christopher

                  Ouch —

                   

                  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…

                  • 6. Re: Filter Extract by Multiple Material Number Dimension
                    Jeff Lefeld


                    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.

                    • 7. Re: Filter Extract by Multiple Material Number Dimension
                      Jonathan Drummey

                      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.

                       

                      Jonathan