2 Replies Latest reply on Oct 7, 2016 4:49 AM by Anthony Griffiths

    Excel Legacy Help

    Anthony Griffiths

      Hi everyone,

       

      I'm trying to add a rank function into the Excel legacy custom sql query:-

       

      Select

       

      *,

      RANK() OVER (ORDER BY [Cost] DESC) as CostRank

       

      From [RawData$]

       

      I'm getting the following error messages:

       

      Database error 0x80040E14: Syntax error (missing operator) in query expression 'RANK() OVER (ORDER BY [Cost] DESC)'.

      ; The query: SELECT TOP 1 * FROM ( Select *, RANK() OVER (ORDER BY [Cost] DESC) as CostRank From [RawData$] ) [Custom SQL Query]

       

      I'm not sure what I'm doing wrong here, or does Legacy not allow this?

       

      Basically I have a spreadsheet that contains cost related data

       

      Scheme NameCostCost Benefit
      ABC£10£1
      XYZ£20£10

       

       

      I want to rank it accordingly so that it should look like:-

       

      ABC | £10 | £1 | 1

      XYZ | £20 | £10 | 2

       

      Hope this makes sense, I can't post the data due to sensitivity

       

      Thank in advance

       

      Ant

        • 1. Re: Excel Legacy Help
          Carl Slifer

          Hi Anthony,

           

          Custom SQL is Jet SQL in the legacy connection and Jet SQL did not have Rank functions. When you bring these values into tableau you should then be able to use Tableau's Rank function.  I believe RANK() has existed since tableau 8.1.

           

          Best Regards,

          Carl Slifer

          InterWorks

          • 2. Re: Excel Legacy Help
            Anthony Griffiths

            Hi,

             

            I figured this was the case

             

            The rank was more of a pre-requisite to creating a running total based on the rank so that I can do some what-if analysis to it

             

            Essentially saying, using the above example, if I invested £x (based on column Cost), how much Cost Benefit would I get in return (Column Benefit Cost)

             

            £x being a parameter

             

            So, in the above example, if I invested £10 I would get £1 Benefit Cost but if I invested £30 I would get £11 Benefit cost