Invalid Expression

[#1]

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:

PreSequence	Math	Info Ao
123	-6	Pay Bill
123	-2	Pay Service
123	2	Pay Old Bill
123	4	New 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.

[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;

[#3]

That did not work. I commented out every line and ran the script through the debugger. It looks as if the something is wrong with the 'Math'(aka FinalMath) field. I created this field bydoing the following in a table before the FINALX table:

Num([1103 Position]) as [1103 Position],

  num([1115 Position]) as [1115 Position],

  num([1115 Position] - [1103 Position]) as Math,

[sunny_talwar]

I don't see anything wrong in the three lines of code you have posted. Can you share your complete script?

[#5]

I actually figured out another way to do what I wanted. Thank you for the help! I may revisit your idea later, because the way I solved the problem is run time intensive.