Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Contributor III

order by clause does not working

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?

Tags (1)
1 Solution

Accepted Solutions
Highlighted

Re: order by clause does not working

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;

View solution in original post

5 Replies
Highlighted

Re: order by clause does not working

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;

View solution in original post

Highlighted

Re: order by clause does not working

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 ..;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Contributor III

Re: order by clause does not working

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

Highlighted

Re: order by clause does not working

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?

Highlighted
Contributor III

Re: order by clause does not working

What about using order by in a preceding load?