Discussion board where members can get started with QlikView.
How to implement bellow one in qlikview.
I have a table with bellow table.
how to get MAX(REC) in descending order by P_CD/YEAR.
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
ORDER BY P_CD ASC, YEAR ASC, REC DESC;
DROP TABLE YourTable;
or if you also want YEAR sorted descending
ORDER BY P_CD ASC, YEAR DESC, REC DESC;
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.
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)
(html, codepage is 1252, embedded labels, table is @1);
//Right join to only keep those observation which match
Right Join (Table)
Max(REC) as REC
Group By ID, P_CD, YEAR;
or you can use this to keep all observations, but assign a flag to those which are Max.
//Left join to add a flag to those observation which are max
Left Join (Table)
Max(REC) as REC,
1 as Flag
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.
Thanks for your reply.
I need MAX(REC) in descending order and this is by P_CD and YEAR
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.
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:
LOAD P_CD, YEAR, REC as MaxRec, P_CD & YEAR as TMPKEY
WHERE NOT EXISTS(TMPKEY, P_CD & YEAR)
ORDER BY REC DESC;
DROP FIELD TMPKEY;
Like Stefan mentioned, would you be able to put down the expected output for the three rows you have provided?