Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Can't Sort, Order By not working

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

1 Solution

Accepted Solutions
Highlighted
mov
Esteemed Contributor III

Re: Can't Sort, Order By not working

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;

View solution in original post

4 Replies
Highlighted
mov
Esteemed Contributor III

Re: Can't Sort, Order By not working

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

Highlighted
Not applicable

Re: Can't Sort, Order By not working

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.

Highlighted
mov
Esteemed Contributor III

Re: Can't Sort, Order By not working

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;

View solution in original post

Highlighted
Not applicable

Re: Can't Sort, Order By not working

It worked! Thanks a lot Michael!