1 2 Previous Next 24 Replies Latest reply on Aug 9, 2013 2:06 PM by Dan Cory

    How Can I Display Records Where Multiple Criteria Are Met?

    mark bologna

      Our data is Excel-based.  One of the fields is labeled Flash.  We use approximately 50 different flashes, and most records have multiple flashes.  I need to be able to count and display the records in Tableau with the ability to show only records matching a given flash.  I've created individual calculated fields for each flash, but the problem is we now have so many quick filters the dashboard has gotten really slow.  I've also tried writing a master formula using IF>THEN statements, but the problem there is the order in ehich you list the flashes in the statement.

       

      Ideally, I could display a graph showing a circle for each record, with an easy way (color?) of knowing which flash(es) it had.  Then, instead of using Quick Filters, I would use actions to narrow down to the details for those records.  The challenge is when one record has multiple flashes.  I also thought about breaking out each of the flashes into their own column (field) in the source data, but I would prefer to avoid that option.

       

      I've attached a very simplified packaged workbook by way of example.  It may be too simple to accurately portray the problem.

       

      Thanks,

      Mark

        • 1. Re: How Can I Display Records Where Multiple Criteria Are Met?
          Dan Cory

          Fun question, Mark.

           

          Tableau really wants each of the flashes to be on a separate row. The attached workbook uses custom SQL to join your original data to a list of all possible flashes, then reduces to just the flashes that apply to that row. Then you can build the workbook you want.

           

          Be careful, though, if you select multiple flashes, the same item may be counted more than once.

           

          This only works well if the list of flashes is known ahead of time. If the list is completely dynamic, it's hard to make this approach work.

           

          Dan

          • 2. Re: How Can I Display Records Where Multiple Criteria Are Met?
            mark bologna

            How/where can I see the custom SQL?  I appreciate the answer, and am aware Tableau would prefer a separate row of data for each flash.  Problem is most of our records have 4-10 flashes each, so that would take our daily import of data from 13,000 records to 50-100,000 each day.  It would quickly become impractical.

             

            I am specifically trying to allow for the possibility the record could be counted in two or more groups.  Sometimes I want to know how many records have a flash.  Other times, I need to know how many have a specific flash.

             

            Any way i can do this without the custom SQL?

             

            Thanks again!

            Mark

            • 3. Re: How Can I Display Records Where Multiple Criteria Are Met?
              Dan Cory


              You can see the custom SQL by editing the connection. But here it is for you:

              SELECT ['7-30-13$'].[File Date] AS [File Date],

                ['7-30-13$'].[File Number] AS [File Number],

                ['7-30-13$'].[Flash] AS [Flash],

                ['7-30-13$'].[LANE] AS [LANE],

              [Sheet1$].[Flash] AS [TheFlash]

              FROM ['7-30-13$'] INNER JOIN [Sheet1$] ON INSTR(['7-30-13$'].[Flash],[Sheet1$].[Flash])>0

               

              50-100,000 records a day isn't very many. Tableau shouldn't have any problem with that amount of data, especially if you use an extract.

               

              There's no way to do this without the custom SQL, other than to transform the data into the proper format outside of Tableau.

               

              Dan

              1 of 1 people found this helpful
              • 4. Re: How Can I Display Records Where Multiple Criteria Are Met?
                mark bologna

                Thanks!  We keep about a total of 330,000 records in Tableau at any one time, all of it extracted from Excel.  We have plenty of quick filters which we are trying to eliminate (hence the reason for this question about the flashes), but the workbook really seems to struggle in terms of speed at this level.  Again, it may be due to the number of quick filters (and we are still on Tableau 7.0).  Even if we fix the speed issue in Tableau, the Excel spreadsheet also really slows with that number of rows.

                • 5. Re: How Can I Display Records Where Multiple Criteria Are Met?
                  Dan Cory

                  You should definitely upgrade to Tableau 8.0 if you can as we made a lot of performance improvements. If not, make sure you get the latest maintenance release of 7.0. There were some performance fixes around 7.0.6 that might help you if you don't already have them.

                   

                  Dan

                  • 6. Re: How Can I Display Records Where Multiple Criteria Are Met?
                    mark bologna

                    Dan - If I understand correctly, you made a 2nd tab in the data spreadsheet, probably with 1 column called The Flash? You then populated it with the list of all possible flashes?  The flashes actually come in our daily internal report with numeric codes.  We then run a macro in Excel to convert the codes to English.  Is it more efficient to do this in Excel or is there a way to do a lookup in Tableau?  Thanks for all your help!

                    • 7. Re: How Can I Display Records Where Multiple Criteria Are Met?
                      Dan Cory

                      You can do the lookup in Tableau if you want. You can join the lookup table in your query (requiring somewhat fancier custom SQL). You can also blend the data with your lookup table. Or if the set of numeric codes doesn't change very often, you can just use aliases on the columns.

                       

                      One issue you might run into if you use numeric codes is that the custom SQL might not work quite right. Right now it just looks for the string anywhere in the output, so 1 will match 1, 21, 12, 212, etc. That's probably not a problem for text (names of your flashes probably don't overlap), but may be a big problem for numbers. Perhaps you can search for ,1, instead, but then you need to make sure to add a comma at the beginning and end of your list.

                       

                      Switching to numeric codes with aliases is probably going to be somewhat faster, but maybe not a lot faster. The biggest improvement might be in how long it takes to build the extract.

                       

                      Good luck!

                       

                      Dan

                      • 8. Re: How Can I Display Records Where Multiple Criteria Are Met?
                        mark bologna

                        How would you do a lookup table in Tableau?

                        • 9. Re: How Can I Display Records Where Multiple Criteria Are Met?
                          mark bologna

                          Dan:

                           

                          Still trying to understand the data on this one.  Can you post the Excel spreadsheet you created showing the columns or tabs with the flashes?  I am a complete SQL newbie and just cannot visualize what the data in the source now looks like.

                           

                          Thanks,

                          Mark

                          • 10. Re: Re: How Can I Display Records Where Multiple Criteria Are Met?
                            Dan Cory

                            Sorry. The Excel spreadsheet is inside the TWBX. You can get it out by right clicking and choosing unpackage, or just rename .twbx to .zip and then unzip. But I attached it here as well.

                            1 of 1 people found this helpful
                            • 11. Re: Re: Re: How Can I Display Records Where Multiple Criteria Are Met?
                              mark bologna

                              Dan - Thanks!

                               

                              I finally understand it.  Now I realize the problem is we do have many flashes that overlap.  I've attached a spreadsheet listing them all, but for instance, many have the word 'converted' in them.  I was able to dupliate the basic custom SQL, but I think I'm in over my head trying to learn how to write the code to look for each exact string of text.

                               

                              Alternatively, I could leave the flashes as numbers (the attached list is what they are translated into after running a macro).  I would still have the problem of the query picking up every instance of the number 1, even if the code was 132.  Am I basically out of luck?  My goal was to be able to plot out every pending record, then filter by the charateristic labeled flash.  If the record had multiple characteristics it would display more than once.

                              • 12. Re: Re: Re: How Can I Display Records Where Multiple Criteria Are Met?
                                Dan Cory

                                With the codes as either numbers or text, you just need to be careful about semicolons.

                                 

                                Suppose your flashes remain in the format you sent first - Flash1; Flash2; Flash3. The the custom SQL is:

                                SELECT ['7-30-13$'].[File Date] AS [File Date],

                                  ['7-30-13$'].[File Number] AS [File Number],

                                  ['7-30-13$'].[Flash] AS [Flash],

                                  ['7-30-13$'].[LANE] AS [LANE],

                                [Sheet1$].[Flash] AS [TheFlash]

                                FROM ['7-30-13$'] INNER JOIN [Sheet1$] ON INSTR("; "+['7-30-13$'].[Flash]+"; ","; "+[Sheet1$].[Flash]+"; ")>0

                                 

                                If you don't have spaces after the semicolons, then don't put them in the strings above either.

                                 

                                The idea is that instead of looking for the string "Flash1", you look for the string "; Flash1; " so that you don't pick up something called "Flash1a" or "Flash18". You need to make sure the first and last flash are surrounded with semicolons as well.

                                 

                                The same strategy will work for numbers.

                                 

                                Dan

                                • 13. Re: Re: Re: Re: How Can I Display Records Where Multiple Criteria Are Met?
                                  mark bologna

                                  Dan - I sure appreciate the patience!  I am getting closer, but every time I try to copy or retype the custom SQL, I get an error.  Is there some trick?  Should I be putting the ' sign at the start and end of every field?  About to pull my hair out.  I can definitely open the twbx you created, and can add data to existing fields in the spreadsheet, but I cannot add additional fields and have them show up in Tableau.  I tried adding the new fields in the custom SQL, but no luck.

                                   

                                  Here's a recap:  My data is pulled down daily from the source into Excel.  One column is labeled Flash and is presented in a series of codes separated by two "~~'.  A sample looks like this:  ~115~~123~~136~~204~

                                   

                                  There is another field labeled Special Issues where data is presented the same way, but there are different numeric codes.


                                  To convert the flashes and special issues, someone created a macro which is stored by itself in a spreadsheet.  You click a button, it asks which of the open Excel files you want to convert, then it does its thing.  The macro spreadsheet has two tabs, one for flashes and one for special issues.  Each of those tabs has 4 columns.  I've attached the converter spreadsheet.

                                   

                                  Here's what I want to do.  Each day I download the records.  I keep those in a tab by day for about two weeks.  I take the records for the day and append them into a tab called summary.  The Summary tab then becomes the source for Tableau.  I would like to add tabs for Special Issues and Flashes, then use the custom SQL in Tableau to join them.  I can either run the converter in Excel and convert the data from this: ~115~~123~~136~~204~ to English or I can leave the data as is and do the join in Tableau which should look up the flash or special issue from the appropriate tab in the source spreadsheet.  I am open to ideas as to whether it is best to run the converter in Excel or just leave the data as numeric and have the join in Tableau via custom SQL 'convert' the numbers to text labels.  I just cannot seem to make the jump from the example to my full-scale data with additional fields.  I've attached a new sample of source data with all the fields using dummy data.

                                   

                                  Thanks again!

                                  Mark

                                  • 14. Re: Re: Re: Re: Re: How Can I Display Records Where Multiple Criteria Are Met?
                                    Dan Cory

                                    The ~115~ format will work well. I see some further complications though:

                                    1. The list of flashes has to be in the same file as the summary since we can only join sheets within the same workbook.

                                    2. The Special Issues and Flashes will create some messiness. If a record has special issues ~1~~2~ and flashes ~3~~4~ then it will appear in the output four times: Once with ~1~ and ~3~, again with ~1~ and ~4~, again with ~2~ and ~3~, and again with ~2~ and ~4~

                                    3. You had the Special Issues and Flashes listed in the format ~1~ in one place and ;1; in the other. It will be better if they are consistent. We could fix this up in the query with the REPLACE function but that will just make it slower. I just fixed up the sheet that I added to the Summary workbook.

                                     

                                    The resulting workbook is attached. I included the XLSX file separately as well.


                                    Dan

                                    1 2 Previous Next