Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

mwallman
Contributor

Why doesn't Order By work with Excel data?

Hi,

Is it me or does Order By doesn't work with Excel data loads?

I tried it with numerous files and it returns a garbage after statement message.

Is this a bug?

I know the fix is to do a resident load and then order by but just curious.

6 Replies

Re: Why doesn't Order By work with Excel data?

You can do a Order By on Resident Load of Excel data, but direct Order By on Excel or QVD load doesn't work.

Re: Why doesn't Order By work with Excel data?

No, it's not bug. It's documented as being a limitation of Order By.

-Rob

Not applicable

Re: Why doesn't Order By work with Excel data?

In Qlik script, we can only use Order by clause in Resident Loads only.

hirishv7
Honored Contributor

Re: Why doesn't Order By work with Excel data?

Hi,

May be like this,

Data:

LOAD Customer,

     [Sales Order ID],

     ShipDate as Date,

  Product,

     Sales,

     Quantity,

      RecNo() AS SortingNumber

FROM

[Sales Orders.xls]

(biff, embedded labels, table is [Sales Orders$]) Where RecNo()<=20;


With out using order by sort according to your master field like above,

With Out Order By.PNG

HTH,

PFA,

Hirish

MVP
MVP

Re: Why doesn't Order By work with Excel data?

Hi,

Order by only works for Resident Load, it won't work for QVD or flat files.  To achieve you have to try like below

DataTemp:

LOAD

*

FROM Data.xls;

Data:

Noconcatenate

LOAD

*

RESIDENT DataTemp

ORDER BY SomeFieldName;

DROP TABLE DataTemp;

Hope this helps you.

Regards,

jagan.

Re: Why doesn't Order By work with Excel data?

A screenshot that goes with the above replies. Taken from the LOAD statement article in QV Desktop Help (the same is available in the QlikView Reference Manual)

Help on Order By thread201352.jpg

Both have identical (and incorrect) formatting for the ORDER BY clause. It doesn't stand out, which makes it easily overlooked. See the part that starts with "This clause may only be used..."

Best,

Peter

Community Browser