How to show max values row in straight table?

[#1]

Hi,

I have below data in qvw. I need to show only max date row in straight table. can anyone suggest me how to do this. I have used MAX(DATE) but it is not working.

INPUT:

ID	NAME	ENTER	LEAVE	DATE	AS NAME	REASON OFF	MAX DATE
123	ADAM	TYR	REG	21/10/2013   16:58	XX	-	01/04/2014   19:24
123	ADAM	CSA	CD	21/10/2013   16:58	XX	-	19/05/2014   15:51
123	ADAM	LG	AVE	21/10/2013   16:58	XX	-	21/10/2013   16:58

need OUTPUT as below

ID	NAME	ENTER	LEAVE	DATE	AS NAME	REASON OFF	MAX DATE
123	ADAM	CSA	CD	21/10/2013   16:58	XX	-	19/05/2014   15:51

Thanks.

[alexandros17]

But date is the same for all the records, which one I must choose?

[#3]

I need max date from MAX DATE column.

[JonnyPoole]

Hi Amelia,

Here i transformed 'Max Date' as a timestamp format in the QlikView load

Then i use Set Analysis to pre-filter the table to only give me the records where Max Date = max( Max Date)

Load expression

LOAD ID,

    NAME,

    ENTER,

    LEAVE,

    DATE,

    [AS NAME],

    [REASON OFF],

    Timestamp#([MAX DATE],'DD/MM/YYYY hh:mm') AS [MAX DATE]

chart expression

=only(  {<[MAX DATE]={"=[MAX DATE]=timestamp(max( total [MAX DATE]))"}>}  [MAX DATE]  )

[dclark0699]

You'll have to make sure the format ends up matching, but something like this in an expression should return what you want

ONLY({<MAX_DATE = {"$(=MAX(MAX_DATE))"}>} MAX_DATE)

Such as in the attached qvw

[#6]

when I used table is showing latest max date. there are millions of IDs. I have added sample one ID. how to do this for all IDs max dates. Please help.

[#7]

when I used table is showing latest max date. there are millions of IDs. I have added sample one ID. how to do this for all IDs max dates. Please help.

[maxgro · Accepted solution]

if you only have 1 expression, when null Qlik doesn't show the row

if([MAX DATE]=max(TOTAL <ID> [MAX DATE]), [MAX DATE])

[#9]

Thanks. That's worked.