Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

order by clause.

Hi all,

How to implement bellow one in qlikview.

I have a table with bellow table.

IDP_CDSHRT_TXTYEARREC
1000XXOG1

12882

1000XXGG144671
1000XXHH277798

how to get MAX(REC) in descending order by P_CD/YEAR.

Thanks

6 Replies
swuehl
MVP
MVP

Maybe like this (assuming above has alread been loaded into data model as a resident YourTable table)?

LOAD ID, P_CD, SHRT_TXT, YEAR, REC

RESIDENT YourTable

ORDER BY P_CD ASC, YEAR ASC, REC DESC;

DROP TABLE YourTable;

or if you also want YEAR sorted descending

LOAD ID, P_CD, SHRT_TXT, YEAR, REC

RESIDENT YourTable

ORDER BY P_CD ASC, YEAR DESC, REC DESC;

DROP TABLE YourTable;

If your original table source supports a ORDER BY clause (like a DB), you can use the ORDER BY clause also in your original YourTable LOAD statement.

sunny_talwar

Is this what you are looking for Hari?

Capture.PNG

I am not sure I follow why we need any order by statement to get this?

You can use this code (without any order by)

Table:

LOAD ID,

     P_CD,

     SHRT_TXT,

     YEAR,

     REC

FROM

[https://community.qlik.com/thread/217833]

(html, codepage is 1252, embedded labels, table is @1);

//Right join to only keep those observation which match

Right Join (Table)

LOAD ID,

  P_CD,

  YEAR,

  Max(REC) as REC

Resident Table

Group By ID, P_CD, YEAR;

or you can use this to keep all observations, but assign a flag to those which are Max.

Table:

LOAD ID,

     P_CD,

     SHRT_TXT,

     YEAR,

     REC

FROM

[https://community.qlik.com/thread/217833]

(html, codepage is 1252, embedded labels, table is @1);

//Left join to add a flag to those observation which are max

Left Join (Table)

LOAD ID,

  P_CD,

  YEAR,

  Max(REC) as REC,

  1 as Flag

Resident Table

Group By ID, P_CD, YEAR;

to get this:

Capture.PNG

So unless you are using Peek/Previous method to get the flag or remove the observation, I don't think Order by is even needed.

And agree with Stefan, that if you are bringing this over from an database such as Oracle, you are better off doing some of these things there and only bring things which are required.

HTH

Best,

Sunny

Not applicable
Author

Hi Sunny,

Thanks for your reply.

I need MAX(REC) in descending order  and this is by P_CD and YEAR

Thanks

swuehl
MVP
MVP

Sunny is probably right that you want to GROUP BY your records, because you seem to want aggregate using Max(REC).

But what do you mean with descending order by P_CD, YEAR then?

If your request is not resolved by Sunny's solution, then please elaborate your requested result table.

swuehl
MVP
MVP

And you need this in the load script? It would be easier to understand what you want if you could post your requested result.

Maybe another option could be:

RESULT:

LOAD  P_CD, YEAR, REC as MaxRec, P_CD & YEAR as TMPKEY

RESIDENT YourTable

WHERE NOT EXISTS(TMPKEY, P_CD & YEAR)

ORDER BY REC DESC;


DROP TABLE YourTable;

DROP FIELD TMPKEY;

sunny_talwar

Like Stefan mentioned, would you be able to put down the expected output for the three rows you have provided?