topic Re: Record for Max() in QlikView

Record for Max()

Tue, 04 Mar 2014 11:42:47 GMT

Hi All,

I want to fetch only those records from the DB where the RUN_ID=maximum.

e.g-I have five fields like ID,SF,NATION,STATE,RUN_ID where RUN_ID=1,2,3.. etc.

I want to pull the records for ID,SF,NATION,STATE if(RUN_ID=3)Everytime i refresh,i don't want to update this RUN_ID.

Can i use Max(Run_ID) or any suggestions?

tresB — Tue, 04 Mar 2014 11:52:42 GMT

Though I did not understand exactly what you meant by:

Everytime i refresh,i don't want to update this RUN_ID.

In a generic way, you can try like:

Max:

Load

Max(RUN_ID) as MaxID

from <>;

Let vMax=Peek('MaxID');

Load

From <> Where RUN_ID=$(vMax);

Drop Table Max;

Tue, 04 Mar 2014 11:53:06 GMT

you can use a sub-query:

LOAD *;

SQL

select ID,SF,NATION,STATE,RUN_ID

from table

where RUN_ID = (select MAX(RUN_ID) from table);

Thanks,

Angad

alexandros17 — Tue, 04 Mar 2014 11:55:26 GMT

Hi,

in the script is easier than in objects:

Suppose that your table (myTable) has field ID, SF,NATION,STATE as primary Key:

TempTab:

Load ID,SF,NATION,STATE Max(RUN_ID) as RUN_ID Resident myTable group by ID,SF,NATION,STATE;

Then do:

Load * resident myTable;

left join

Load *, '1' as flagMax resident TempTab;

Drop table temptab;

Now you can use in object simply the flag "flagMax" to write needed rows

Hope it helps