Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
You can do a Order By on Resident Load of Excel data, but direct Order By on Excel or QVD load doesn't work.
No, it's not bug. It's documented as being a limitation of Order By.
-Rob
In Qlik script, we can only use Order by clause in Resident Loads only.
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
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.
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