Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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,
I don't see anything wrong in the three lines of code you have posted. Can you share your complete script?
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.