3 Replies Latest reply on Apr 1, 2015 1:32 AM by kettan

    Cross Join Collection with just one table

    manuel.vanbalencedeno

      Hi there,

       

      First of all, please excuse me if I'm too much of a newbie.

       

      I've been stuck for days on what seems to be the most common question around: The whole open and close date, Cross Join Collection.

       

      I have a table with Project Name, Start Date and End Date, and would like to create a bar chart telling me how many projects are active on each month of the year.

       

      The issue that I'm having is that on the Cross Join Collection document, methods one and two (which would be the useful ones for me) are all about joining two tables together. However, what should I do if I can't create that second lookup table with all the dates?

       

      I see how on excel it would be pretty easy to create another table just by creating another sheet. But if I'm getting my data from a server and can't really add new tables to it, what is the way to proceed? I tried creating the table in Excel and then adding that as an additional data source and then joining the tables, but I was not able to join tables from different data sources.

       

      Also, I know that I'm pushing my luck and your patience, but would it be possible to get a text explanation and not an attached workbook? I have Tableau 8.0 and can't open most of the workbooks posted as they tend to be from newer versions.

       

      Thank you very much for any help.

       

      Cheers!

        • 1. Re: Cross Join Collection with just one table
          kettan

          CROSS JOINs can be done without a lookup table in the database as explained under the title TABLE-LESS LOOKUP TABLEs in the introduction section of  The Cross Join Collection

           

          Here is the syntax for MS SQL Server, Oracle, and Jet SQL (Excel, Text, Access):

           

          MS SQL Server

           

          SELECT *
          FROM [data$] d,
          (select '2014-01-01' as [Month Start], '2014-01-31' as [Month End] union all
          select '2014-02-01', '2014-02-28' union all
          select '2014-03-01', '2014-03-31' union all
          select '2014-04-01', '2014-04-30' union all
          select '2014-05-01', '2014-05-31' union all
          select '2014-06-01', '2014-06-30' union all
          select '2014-07-01', '2014-07-31' union all
          select '2014-08-01', '2014-08-31' union all
          select '2014-09-01', '2014-09-30' union all
          select '2014-10-01', '2014-10-31' union all
          select '2014-11-01', '2014-11-30' union all
          select '2014-12-01', '2014-12-31'
          ) l
          WHERE d.[Start Date] <= l.[Month End]
          and d.[End Date] >= l.[Month Start]
          
          
          
          
          
          
          
          
          

           

           

          Oracle

           

          SELECT *
          FROM [data$] d,
          (select '2014-01-01' as [Month Start], '2014-01-31' as [Month End] from dual union all
          select '2014-02-01', '2014-02-28' from dual union all
          select '2014-03-01', '2014-03-31' from dual union all
          select '2014-04-01', '2014-04-30' from dual union all
          select '2014-05-01', '2014-05-31' from dual union all
          select '2014-06-01', '2014-06-30' from dual union all
          select '2014-07-01', '2014-07-31' from dual union all
          select '2014-08-01', '2014-08-31' from dual union all
          select '2014-09-01', '2014-09-30' from dual union all
          select '2014-10-01', '2014-10-31' from dual union all
          select '2014-11-01', '2014-11-30' from dual union all
          select '2014-12-01', '2014-12-31' from dual
          ) l
          WHERE d.[Start Date] <= l.[Month End]
          and d.[End Date] >= l.[Month Start]
          
          
          
          
          
          

           

           

          Jet SQL (Excel, Text, Access)

           

          SELECT *
          FROM [data$] d,
          (select top 1 #2014-01-01# as [Month Start], #2014-01-31# as [Month End] from [data$] union all
          select top 1 #2014-02-01#, #2014-02-28# from [data$] union all
          select top 1 #2014-03-01#, #2014-03-31# from [data$] union all
          select top 1 #2014-04-01#, #2014-04-30# from [data$] union all
          select top 1 #2014-05-01#, #2014-05-31# from [data$] union all
          select top 1 #2014-06-01#, #2014-06-30# from [data$] union all
          select top 1 #2014-07-01#, #2014-07-31# from [data$] union all
          select top 1 #2014-08-01#, #2014-08-31# from [data$] union all
          select top 1 #2014-09-01#, #2014-09-30# from [data$] union all
          select top 1 #2014-10-01#, #2014-10-31# from [data$] union all
          select top 1 #2014-11-01#, #2014-11-30# from [data$] union all
          select top 1 #2014-12-01#, #2014-12-31# from [data$]
          ) l
          WHERE d.[Start Date] <= l.[Month End]
          and d.[End Date] >= l.[Month Start]
          
          
          
          
          
          
          
          
          

           

          Ps. I have Tableau Desktop 4.0, 4.1, 5.0, 5.1, 5.2, 6.0, 6.1, 7.0, 8.0, 8.1, 8.2, 8.3, and 9.0 Beta installed on my laptop 

          .

          • 2. Re: Cross Join Collection with just one table
            manuel.vanbalencedeno

            Kettan, you are a legend.

             

            I had read the bit about the Table-Less lookup but as I said, I'm a newbie and it didn't make much sense to me. Seeing some actual code makes it easier (I'll still have to do some research to fully understand it)

             

            However, I'm still too much of a rookie and can't seem to figure out how to actually implement the code.

             

            When I work with excel data sources I can just go to Data > My data source > Edit Connection > Custom SQL. However, now that I'm working on the database (MS SQL Server, by the way), I can't see that option so I don't know where to actually input the SQL.

             

            Just in case, I've added a very simple workbook with dummy data from excel trying to show what I have and what I want. Just in case someone is not clear on that.

             

            Thanks a lot for all the help.

            • 3. Re: Cross Join Collection with just one table
              kettan

              I can't try this out myself  1) because I don't have MS SQL Server on my computer and  2) because I only have Desktop Personal which cannot connect to databases.

               

              That said, I think the  MS SQL Server example in OnlineHelp 8.0  has the information you need. In step 5 you choose Custom SQL and paste the code into it (assuming you have tested it first in a query tool).

              MS SQL Server Tableau 8.0 Custom SQL.png