1 2 3 Previous Next 32 Replies Latest reply on Nov 17, 2015 9:00 AM by James Carter Go to original post
      • 15. Re: Tableau & SQL Group
        Toby Erkson

        NOT is related to SQL.  I use it in my SQL code.  To not have NOT would not make sense

         

        Shawn, I agree, where the heck is the function reference guide?

        • 16. Re: Tableau & SQL Group
          kettan

          Although I rarely work with text files, I value very much a tip by Robin Kennedy that "text files in the same directory are treated like tables in a database". Read complete comment below:   bold + underline emphasizes mine

          Re: Strategy for working across multiple data sources


          I think what you need here is to combine your data using a UNION, not a join. If they are text files (csv?), then you can perform a union on each one so long as they are in the same directory (they are then treated like tables in a database).


          For example if you want to combine Jan and Feb data, connect to Jan, then edit the connection and write a custom SQL statement such as follows:

           

          SELECT [Jan#txt].[amount] AS [amount],

            [Jan#txt].[date] AS [date],

            [Jan#txt].[status] AS [status]

          FROM [Jan#txt]

          UNION

          SELECT [Feb#txt].[amount] AS [amount],

            [Feb#txt].[date] AS [date],

            [Feb#txt].[status] AS [status]

          FROM [Feb#txt]

           

          If you want more months then juct keep going with more UNIONS.

           

          There's more information here... the article relates to a spreadsheet, but the method is more or less the same.http://kb.tableausoftware.com/articles/knowledgebase/union-related-data-across-multiple-excel-tabs

          • 17. Re: Tableau & SQL Group
            Eric McDonald

            Reversed LIKE Search


            It's worth remembering that the:


            WHERE <Parameters.Region Parameter> LIKE '%' + [Region] + '%'


            syntax can have an expensive performance hit as the database has to calculate the LIKE part for every row.

            • 18. Re: Tableau & SQL Group
              kettan

              Thanks Eric, this comment is appreciated and true, particularly for bigger tables.

               

              One scenario that performs well on bigger tables is when we join them with a small dimension table, because LIKE is calculated against the small dimension table and therefore not expensive.


              I just tested this on SQL Server 2012 with a small dimension table (231 rows) joined with a big fact table (8M rows and 726 MB). There was no performance difference between following condition types (both costed 2 seconds):

               

              Normal LIKE Search

              WHERE [Region] like '%East%' or [Region] like '%South%'

               

              Reversed LIKE Search

              WHERE 'East, South' LIKE '%' + [Region] + '%'

              • 19. Re: Tableau & SQL Group
                kettan

                Tableau Online Help can be used as function reference guide:

                 

                     Functions

                     Operators  NOT included 

                • 20. Re: Re: Tableau & SQL Group
                  kettan

                  Multi-Item String Search w/ Reversed Contains Search

                  Reversed CONTAINS Search.png

                  The technique used is similar to Reversed LIKE Search and therefore shared here.

                  Personally I am fond of this technique.

                   

                  See more in attached workbook in Re: Using a parameter and a filter on a string

                  where this technique is suggested as a multi-string search solution.

                  • 21. Re: Tableau & SQL Group
                    Eric McDonald

                    Oh dear Venn diagrams again


                    However these only use 2 circles so they work.

                    • 22. Re: Tableau & SQL Group
                      Toby Erkson

                      Eric, what in the heck are you talking about.  I haven't perfected my mind-reading skills yet, much to my wife's dismay

                      • 23. Re: Tableau & SQL Group
                        Brian Calvanese

                        Check out my recent post re: dynamic self join:

                         

                        Flux Analysis // Dynamic User-Defined Self-Join

                         

                        Useful for analyzing changes in time-stamped data without going cartesian...

                        • 24. Re: Tableau & SQL Group
                          Eric McDonald

                          Weird, my post re: Venn diagrams must have been posted in wrong thread. Oops!

                          • 25. Re: Tableau & SQL Group
                            Andy Holt

                            I am trying to create a UNION custom SQL query and it was all going well until I wanted to include an if statement. Does anyone know if that will work?

                            I tried googling and came to the conclusions (for some reason I cannot now remember) that I needed to use iif(...,...,...) but I cannot get it to work.

                             

                            Here's my code:

                             

                            SELECT [fact_sales_daily].[soldto_customer_number] AS [soldto_customer_number],

                              [dim_date].[fin_year] AS [Fin Year],

                              [dim_product].[item_description] AS [item_description],

                              [fact_BOM_components_current].[quantity]*[fact_sales_daily].[quantity_report]*<Parameters.retail price> AS [Sales Value],

                              iif([dim_soldto_customer].[soldto_payment_instrument_code]= 'T',0.985,1) AS [factor]

                            FROM ([dbo].[fact_sales_daily] [fact_sales_daily]

                              INNER JOIN [dbo].[dim_date] [dim_date] ON ([fact_sales_daily].[dim_date_key] = [dim_date].[dim_date_key])

                              INNER JOIN [dbo].[dim_soldto_customer] [dim_soldto_customer] ON ([fact_sales_daily].[dim_soldto_customer_key] = [dim_soldto_customer].[dim_soldto_customer_key])

                              INNER JOIN [dbo].[dim_product] [dim_product] ON ([fact_sales_daily].[dim_product_key] = [dim_product].[dim_product_key])

                              INNER JOIN [dbo].[fact_BOM_components_current] [fact_BOM_components_current] ON ([fact_sales_daily].[dim_organisation_key] = [fact_bom_components_current].[dim_parent_organisation_key]) AND ([fact_sales_daily].[dim_product_key] = [fact_bom_components_current].[dim_parent_product_key])

                            WHERE [dim_date].[fin_year] =<Parameters.Fin Year>

                            AND [dim_date].[fin_month_name] in ('Dec','Jan','Feb','Mar')

                             

                            It all works fine until I put in the last line in the SELECT section (the bit including the IIF).

                             

                            I get an error message saying that I have incorrect syntax near '=', as below.

                             

                            Has anyone got any thoughts? What am I doing wrong?

                             

                            Thanks

                            syntax.PNG

                             

                            Incidentally, I built the equivalent query in MS Access 2010 and it worked fine:

                             

                            SELECT dbo_fact_sales_daily.soldto_customer_number,

                            dbo_dim_date.fin_year,

                            dbo_dim_date.fin_month_no,

                            dbo_dim_product.item_description,

                            [dbo_fact_bom_components_current]![quantity]*[dbo_fact_sales_daily]![quantity_report]*12000 AS [Sales Value],

                            dbo_dim_shipto_customer.shipto_payment_instrument_code, IIf([dbo_dim_shipto_customer]![shipto_payment_instrument_code]='T',0.985,1) AS Factor

                            FROM (((dbo_fact_sales_daily

                            INNER JOIN dbo_dim_date ON dbo_fact_sales_daily.dim_date_key = dbo_dim_date.dim_date_key)

                            INNER JOIN dbo_dim_product ON dbo_fact_sales_daily.dim_product_key = dbo_dim_product.dim_product_key)

                            INNER JOIN dbo_dim_shipto_customer ON dbo_fact_sales_daily.dim_shipto_customer_key = dbo_dim_shipto_customer.dim_shipto_customer_key)

                            INNER JOIN dbo_fact_bom_components_current ON (dbo_fact_sales_daily.dim_product_key = dbo_fact_bom_components_current.dim_parent_product_key) AND (dbo_fact_sales_daily.dim_organisation_key = dbo_fact_bom_components_current.dim_parent_organisation_key)

                            WHERE (((dbo_dim_date.fin_month_name) In ('Dec','Jan','Feb','Mar'))

                            GROUP BY dbo_fact_sales_daily.shipto_customer_number,

                            dbo_dim_date.fin_year,

                            dbo_dim_date.fin_month_no,

                            dbo_dim_product.item_description,

                            [dbo_fact_bom_components_current]![quantity]*[dbo_fact_sales_daily]![quantity_report],

                            dbo_dim_shipto_customer.shipto_payment_instrument_code, IIf([dbo_dim_shipto_customer]![shipto_payment_instrument_code]='T',0.985,1)

                            HAVING (((dbo_dim_date.fin_year)=2014));

                            • 26. Re: Tableau & SQL Group
                              kettan

                              Your error messages mentions SQL Server. Therefore I assume you connect to that.

                               

                              This is Excel syntax:

                               

                              iif([dim_soldto_customer].[soldto_payment_instrument_code]= 'T',0.985,1) AS [factor]
                              

                               

                               

                              This is SQL Server syntax:

                               

                              case when [dim_soldto_customer].[soldto_payment_instrument_code]= 'T' then 0.985 else 1 end AS [factor]
                              
                              • 28. Re: Tableau & SQL Group
                                Andy Holt

                                Thank you

                                • 29. Re: Tableau & SQL Group
                                  Andy Holt

                                  Thank you too