Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
if you only have 1 expression, when null Qlik doesn't show the row
if([MAX DATE]=max(TOTAL <ID> [MAX DATE]), [MAX DATE])
But date is the same for all the records, which one I must choose?
I need max date from MAX DATE column.
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] )
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
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.
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.
if you only have 1 expression, when null Qlik doesn't show the row
if([MAX DATE]=max(TOTAL <ID> [MAX DATE]), [MAX DATE])
Thanks. That's worked.