Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Sort and group in script

Dear Qlik community,

Advise me pls on the question - I need to group Net_Sales in script by Customer, than sort by Net_Sales with descending.

What I'm trying to perform:

LOAD Cust as Customer,

     sum([Sales net]) as Net_Sales

Resident SalesTable

Group by Cust

Order By [Sales net] desc;

Thanks

1 Solution

Accepted Solutions

Re: Sort and group in script

As far as I understand (and I might be wrong about it), you can't group and sort by a new calculated field in the same statement. Here is what's stated in QlikView Help:

================================

order by is a clause used for sorting the records of a resident table before they are processed by the load statement. The resident table can be sorted by one or more fields in ascending or descending order. The sorting is made primarily by numeric value and secondarily by national ASCII value. This clause may only be used when the data source is a resident table

================================

Based on that, you should first group your data, and then reload the same table again in a resident load and sort it using the ORDER BY clause.

cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy and Austin, TX!

3 Replies
MVP
MVP

Re: Sort and group in script

The order by clause is sorting the input records, not the output records.

[From the HELP:

order_by   order by  is a clause used for sorting the records of a resident table before they are processed by the load statement

]

so you would need a subsequent LOAD to order your aggregated values as demanded.

Re: Sort and group in script

As far as I understand (and I might be wrong about it), you can't group and sort by a new calculated field in the same statement. Here is what's stated in QlikView Help:

================================

order by is a clause used for sorting the records of a resident table before they are processed by the load statement. The resident table can be sorted by one or more fields in ascending or descending order. The sorting is made primarily by numeric value and secondarily by national ASCII value. This clause may only be used when the data source is a resident table

================================

Based on that, you should first group your data, and then reload the same table again in a resident load and sort it using the ORDER BY clause.

cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy and Austin, TX!

Re: Sort and group in script

Jinx!

Community Browser