Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Read about Qlik’s Response to COVID-19 Read the Letter, Join the Group.
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

Tags (1)
6 Replies
Highlighted
MVP
MVP

Re: order by clause.

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.

Highlighted

Re: order by clause.

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

Highlighted
Not applicable

Re: order by clause.

Hi Sunny,

Thanks for your reply.

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

Thanks

Highlighted
MVP
MVP

Re: order by clause.

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.

Highlighted
MVP
MVP

Re: order by clause.

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;

Highlighted

Re: order by clause.

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