4 Replies Latest reply: Oct 13, 2016 12:48 PM by Seth Rosenbauer RSS

    Invalid Expression

    Seth Rosenbauer

      Not sure what's going on here in my script.

       

      FINALX:

        Load

            [PreSequence],

            [1103 Position],         

            [1115 Position],

            Math,                               //created by doing the following [1115 Position] - [1103 Position], I did this in a table above

            min(Math) as FinalMath,

            [Info Ao]

        Resident Final

        group by [PreSequence];

        Drop Table Final;

       

      Any ideas? My end goal is the following:

       

      For each distinct PreSequence, there could have values for 'Math' that are the following:

       

      PreSequenceMathInfo Ao
      123-6Pay Bill
      123-2Pay Service
      1232Pay Old Bill
      1234New Service

       

      For each PreSequence, I want to find the Info Ao where the 'Math' value is smallest value that is greater than zero. In this example, that would be 2 and the Info Ao would be 'Pay Old Bill'. Maybe there is an easier way to solve this. I open to suggestions.

        • Re: Invalid Expression
          Sunny Talwar

          I think you are only grouping by PreSequence here and you still have other non aggregated fields in your load statement is the issue here. Try this may be:

           

          FINALX:

            Load

                [PreSequence],                 

                Min(Math) as FinalMath,

                FirstSortedValye([Info Ao], Math) as [Info Ao]

          Resident Final

          Where Math > 0

          Group By [PreSequence];

           

          Drop Table Final;