3 Replies Latest reply on Apr 25, 2016 8:02 PM by G Marc Turner

    convert SQL to Tableau, help!

    c n
    c n Nov 7, 2013 7:37 PM

      Hoping someone who knows  alot more about translating SQL code into Tableau calculated fields can assist. I've been given some SQL code to identfiy the first action date of an order. I need to report in Tableau the distinct number of orders first actioned in a time period. Here is the code:

       

      select order_no, MIN(action_d)Action_d, 1 as Count                                                                                                           

      from (

      select order_no, action_d, cust_no                                                 

      from <table>

      where done='Y'

      group by order_no, action_d, cust_no

      )a                                                                                                        

      group by order_no                                                                                                         

      order by min(action_d)

       

      The <table> is connected and order_no, action_d, done, & cust_no are all available in Tableau as Dimensions/Measures. I just help someone is able to look at this and help me createa calculated field

      • 241 Views
      • Tags:
        • 1. Re: convert SQL to Tableau, help!
          Frances Holland
          Frances Holland Nov 7, 2013 9:29 PM (in response to c n)

          Have you considered just pulling this in as a second data source?  You can paste that directly into the Custom SQL box when you load in the data source.  You could then blend on the date and pull the count(order_no) into your worksheet.

           

          Failing that, can you attach the twbx workbook?  It's a bit hard to visualise without the ability to play around with it.

          • 2. Re: convert SQL to Tableau, help!
            Prashant Sharma
            Prashant Sharma Nov 7, 2013 10:06 PM (in response to c n)

            Hi,

            Just use SQL same as you used. If code is fine, just put table names into [table_name]. Are you getting any type of error? If yes, put snapshot of error you are getting. Hope this help!

             

            Warm Regards,

            Prashant Sharma - India | LinkedIn

            • 3. Re: convert SQL to Tableau, help!
              G Marc Turner
              G Marc Turner Apr 25, 2016 8:02 PM (in response to c n)

              Like others have mentioned, bringing in the list of first action dates as a second data source might be the easiest way of doing this since you already have the code and knows it works. But, blending can be a challenge sometimes. I haven't tried the following, so it might need to be revised in order to work properly. With this in mind, here's is what comes to mind:

               

              IF [done]='Y' and [action_d]={fixed [order_no]:min([action_d])} then 1 end

               

              The portion in {} is a LOD expression to find the minimum action_d by order_no. In this case you need to match the record with that action_d and done='y'. If both of those conditions are met, the value will be 1, otherwise it will be null. Summing (or counting the number of records where it equals 1) on a given date should give you the total number by date.

               

              Again, I haven't tested this so I don't know if it will work or not, but it's what came to mind when reading your question. I would recommend verfiying it against some known data before going to far with it just to make sure it is doing what you want.

               

              -Marc