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

Why doesn't Order By work with Excel data?

mwallman — Mon, 25 Jan 2016 22:59:32 GMT

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.

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

sunny_talwar — Mon, 25 Jan 2016 23:12:00 GMT

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?

rwunderlich — Tue, 26 Jan 2016 01:06:42 GMT

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

-Rob

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

Tue, 26 Jan 2016 02:59:30 GMT

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

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

HirisH_V7 — Tue, 26 Jan 2016 04:09:07 GMT

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,

HTH,

PFA,

Hirish

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

jagan — Tue, 26 Jan 2016 05:27:28 GMT

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?

Peter_Cammaert — Tue, 26 Jan 2016 09:05:51 GMT

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)

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