2 Replies Latest reply on Oct 16, 2013 1:31 PM by Caleb Smith

    SQL "UNION" workaround

    Caleb Smith

      This may be a an easy one--I feel like I'm missing something obvious--but I've been fooling around with this for last 1.5 hours and haven't made any progress.

       

      I'm trying to replicate or approximate a BusinessObjects query I've used in the past that employs unions. I know Tableau doesn't support unions without custom SQL so I'm trying to approximate them through calculated fields. There are four tables that each require a slightly different filtering conditions. I want to get the filtered results from each of these tables and display them in a single aggregated view.

       

      Here is some sample logic from one of the calculated fields I'm trying, and failing, to create:

       

      IF( [status] = "Staff Contingency Review" OR [status] = "Staff Review" OR [status] = "Reviewer Contingency Review" OR [status] = "Committee Decision" OR [status] = Review Decision"

           THEN

      SUM(IF DATEDIFF('day',[RECEIPT_DT],TODAY()) > 7 AND DATEDIFF('day',[RECEIPT_DT],TODAY()) < 15 THEN 1 ELSE 0 END))

       

      In other words:

       

      IF a, b, or c THEN SUM(IF d AND e THEN 1 ELSE 0 END)

       

      This doesn't work, but if it did I would then create a similar calculated field for each of the four tables and would then add them to the view's rows shelf.

       

      Can anyone tell me where I'm going wrong on this?

       

      Thanks!

        • 1. Re: SQL "UNION" workaround
          Matt Lutton

          Are you getting the "cannot mix aggregate..." error?

           

          If so, try wrapping [status] in ATTR. So:

          IF( attr([status]) = "Staff Contingency Review" OR attr([status]) = "Staff Review" OR attr([status]) = "Reviewer Contingency Review" OR attr([status]) = "Committee Decision" OR attr([status]) = Review Decision"

               THEN

          SUM(IF DATEDIFF('day',[RECEIPT_DT],TODAY()) > 7 AND DATEDIFF('day',[RECEIPT_DT],TODAY()) < 15 THEN 1 ELSE 0 END))

          It appears everything else is already aggregated, so it MAY work (no guarantees)

          1 of 1 people found this helpful
          • 2. Re: SQL "UNION" workaround
            Caleb Smith

            I think I might have it now but am waiting for the full data set to extract before I can be sure. I tried your solution and it didn't quite work but it got me thinking about my syntax. I believe now that I should be using IIF instead of IF. This at least tells me that the calculation is valid!

             

            SUM(IIF([STATUS] = "Staff Contingency Review"

            OR [STATUS] = "Staff Review"

            OR [STATUS] = "Reviewer Contingency Review"

            OR [STATUS] = "Committee Decision"

            OR [STATUS] = "Review Decision",

            IF DATEDIFF('day',[RECEIPT_DT],TODAY()) > 7 AND DATEDIFF('day',[RECEIPT_DT],TODAY()) < 15 THEN 1 ELSE 0 END,0))