Can't Sort, Order By not working

[#1]

Hi Everybody,

I'm having an issue with sorting in the script. I need my data to be sorted by POST_DATE and then EMPLOYEE_NUMBER but for whatever reason, the script seems to be ignoring that.

I've tried using the date formatted as numbers, and just the numeric portion of the Employee Number but nothing works.

I feel like I'm just overlooking something but I have no idea what it is. It's driving me crazy.

Can anyone help??

Thanks,

Justin

[Anonymous]

Typically you sort data on the front end objects.  What is the purpose of sorting in the script in this case?

[#3]

I'm going to be adding in expressions to create fields that use the Peek function but the data must be sorted in a certain way for it to work.

[Anonymous · Accepted solution]

OK, in this case you need script level sort.  To make it reliable, sort by a numeric value:

Temp_Data2:

NoConcatenate

LOAD PAY_WK_ENDING,

     POST_DATE,

     POST_MONTH,

     sum(ACTUAL_HOURS_WORKED) as ACTUAL_HOURS_WORKED,

     EMPLOYEE_NUMBER,

    mid(EMPLOYEE_NUMBER,4) as Sort2,          // this is a number

     EVENT

Resident Temp_Data1

group by POST_DATE,PAY_WK_ENDING, POST_MONTH, EMPLOYEE_NUMBER, EVENT;

drop table Temp_Data1;

Temp_Data3:

NoConcatenate

Load

*

Resident Temp_Data2

order by POST_DATE, Sort2;

[#5]

It worked! Thanks a lot Michael!