topic Re: Order by clause in QlikView

Order by clause

vireshkolagimat — Tue, 01 Mar 2016 13:10:33 GMT

Hi,

Why can't we use the Order by clause with load statment. I know that we can use the order by clause with resident load.

Regards,

Viresh

Re: Order by clause

oknotsen — Tue, 01 Mar 2016 13:57:00 GMT

You actually can use the "order by" clause when loading from various sources, including resident.

What is your exact problem?

Re: Order by clause

hubertdudek — Tue, 01 Mar 2016 14:27:31 GMT

You can. If you have any problems you can use preceding load:

LOAD * ORDER BY ....;

LOAD * ....

Re: Order by clause

Peter_Cammaert — Tue, 01 Mar 2016 15:36:48 GMT

His problem probably is that ORDER BY can only be used on RESIDENT tables, not on external file sources (LOAD FROM).

Viresh, that's how QlikView works. Nothing to be done about that.

You can however use the ORDER BY clause in SQL statements any way you like. That may bring some solace, if you use an RDBMS as external data source.

Best,

Peter

Re: Order by clause

Peter_Cammaert — Tue, 01 Mar 2016 17:29:27 GMT

I'm not really sure, but that may well turn your Preceding Load into a Resident Load of a hidden table.

Re: Order by clause

oknotsen — Tue, 01 Mar 2016 19:04:13 GMT

His problem probably is that ORDER BY can only be used on RESIDENT tables, not on external file sources (LOAD FROM).

I guess you mean "only not from files", because I am pretty sure you can also use it with various databases. I probably would not recommend it, but you can.

Anyway,

If the OP wants to sort his data in some sort of a way, wouldn't this be a solution:

TableA:

Load from tableA;

TableB:

noconcatenate

Load * from TableA

order by someField;

drop TableA;

rename table TableB to TableA;

Re: Order by clause

Peter_Cammaert — Tue, 01 Mar 2016 20:24:17 GMT

Of course you're right (see the second statement in my post). But a SELECT is an entirely different beast than a (QlikView) LOAD statement, and as far as I know LOAD doesn't allow for reading RDBMS tables (yet).

A while ago and in another discussion, I posted this snapshot from the QV desktop help. The text is still badly formatted (for a number of years now) so it doesn't do the ORDER BY clause any justice. But it still applies. See the first and fourth sentences: