topic order by clause. in QlikView

order by clause.

Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

How to implement bellow one in qlikview.

I have a table with bellow table.

ID	P_CD	SHRT_TXT	YEAR	REC
1000	XX	OG	1	12882
1000	XX	GG	1	44671
1000	XX	HH	2	77798

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

Thanks

Re: order by clause.

swuehl — Sun, 22 May 2016 11:15:59 GMT

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.

Re: order by clause.

sunny_talwar — Sun, 22 May 2016 13:11:46 GMT

Is this what you are looking for Hari?

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:

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

Re: order by clause.

Sun, 22 May 2016 14:59:53 GMT

Hi Sunny,

Thanks for your reply.

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

Thanks

Re: order by clause.

swuehl — Sun, 22 May 2016 16:25:05 GMT

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.

Re: order by clause.

swuehl — Sun, 22 May 2016 21:17:40 GMT

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;

Re: order by clause.

sunny_talwar — Mon, 23 May 2016 00:44:32 GMT

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