topic Re: How to show max values row in straight table? in QlikView

How to show max values row in straight table?

Wed, 09 Sep 2015 15:06:54 GMT

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.

Re: How to show max values row in straight table?

alexandros17 — Wed, 09 Sep 2015 15:19:39 GMT

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

Re: How to show max values row in straight table?

Wed, 09 Sep 2015 15:30:04 GMT

I need max date from MAX DATE column.

Re: How to show max values row in straight table?

JonnyPoole — Wed, 09 Sep 2015 15:40:47 GMT

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] )

Re: How to show max values row in straight table?

dclark0699 — Wed, 09 Sep 2015 15:42:02 GMT

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

Re: How to show max values row in straight table?

Wed, 09 Sep 2015 15:47:42 GMT

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.

Re: How to show max values row in straight table?

Wed, 09 Sep 2015 15:48:01 GMT

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.

Re: How to show max values row in straight table?

maxgro — Wed, 09 Sep 2015 16:07:41 GMT

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

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

Re: How to show max values row in straight table?

Thu, 10 Sep 2015 09:12:21 GMT

Thanks. That's worked.