topic Re: order by clause does not working in QlikView

order by clause does not working

ananyaghosh — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am using the following code:

[test]:

first 10000

LOAD

PROC_ID,

SEVERITY,

ALERT_ID,

CREATION_DATE as ALERT_CREATE_TIMESTAMP,

//left (timestamp(CREATION_DATE),10) as ALERT_CREATE_DATE,

Left( CREATION_DATE, Index( CREATION_DATE,' ') -1 ) as ALERT_CREATE_DATE,

STATUS_TYPE,

EVENT_ID

From [$(vFinalDest)EDI_CC_ALERT_FULL_LOAD.qvd](qvd)

order by CREATION_DATE desc

;

when I Load the data it gives me error for 'order by CREATION_DATE desc'

can anyone give me suggestion for this?

Re: order by clause does not working

sunny_talwar — Tue, 16 Jan 2018 09:18:45 GMT

You cannot use Order By clause in a Qvd or Excel load... try to fix the order of load while you create the qvd or use it in a resident load like this

[test]:

first 10000

LOAD

PROC_ID,

SEVERITY,

ALERT_ID,

CREATION_DATE as ALERT_CREATE_TIMESTAMP,

//left (timestamp(CREATION_DATE),10) as ALERT_CREATE_DATE,

Left( CREATION_DATE, Index( CREATION_DATE,' ') -1 ) as ALERT_CREATE_DATE,

STATUS_TYPE,

EVENT_ID

From [$(vFinalDest)EDI_CC_ALERT_FULL_LOAD.qvd](qvd);

Final:

NoConcatenate

LOAD *

Resident test

order by CREATION_DATE desc;

DROP Table test;

Re: order by clause does not working

Anil_Babu_Samineni — Tue, 16 Jan 2018 09:20:14 GMT

That is limitation rule for Order By. So, you need Another table loading from one to another like

Final:

NoConcatenate

Load * Resident Table Order By ..;

Re: order by clause does not working

ananyaghosh — Tue, 16 Jan 2018 10:31:30 GMT

Hi,

My 'order by' problem has been solved and one very critical problem has come to me as:-

when i specify 'first 10000' before load statement, I got proper data using the below statement:-

Left( CREATION_DATE, Index( CREATION_DATE,' ') -1 ) as ALERT_CREATE_DATE

but whenever I replace the 'first 10000' code I got nothing in that column.

I have total 7834 rows, so it is not a problem of less amount of data loading. Please give me advise what may cause this problem.

Thanks,

Sandip Ghosh

Re: order by clause does not working

sunny_talwar — Tue, 16 Jan 2018 12:22:00 GMT

That is very strange... I have never heard of this kind of issue before... would you be able to share a sample to show the issue?

Re: order by clause does not working

markp201 — Wed, 17 Jan 2018 22:19:43 GMT

What about using order by in a preceding load?