Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mwallman
Creator III
Creator III

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
sunny_talwar

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

-Rob

Not applicable

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

HirisH_V7
Master
Master

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

HirisH
“Aspire to Inspire before we Expire!”
jagan
Luminary Alumni
Luminary Alumni

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.

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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