topic Load maximum date only in QlikView

Load maximum date only

Thu, 16 Feb 2012 20:22:54 GMT

hi there!

I have question to ask

sample table like this (format date : mm/dd/yyyy):

ID Name Date

1 John 1/6/2011

1 John 3/2/2011

1 John 4/20/2011

2 Andy 2/1/2011

3 Jean 3/3/2011

3 Jean 4/7/2011

4 Chris 1/1/2011

what i want is to load the table where the date is maximum date like this :

ID Name Date

1 John 4/20/2011

2 Andy 2/1/2011

3 Jean 4/7/2011

4 Chris 1/1/2011

how to solve it? thanks all

deepakk — Thu, 16 Feb 2012 21:35:05 GMT

hi,

Use max function with group by clause to get the reuslt.

Load ID, Name, Date(Max(Date),'mm/dd/yyyy') as Date

from table group by ID, Name;

Deepak

Thu, 16 Feb 2012 21:57:48 GMT

Hi,

see also this example for help (Solution by script and by expression in a table)

Good luck!

Rainer

jagan — Fri, 17 Feb 2012 04:53:53 GMT

Hi,

You can load only Max dates by using the script below

Load

ID,

Name,

Date(Max(Date),'mm/dd/yyyy') as Date

FROM FileName

GROUP BY ID, Name;

You can use

Max(Date) in the expression, by using ID and Name as Dimensions.

Regards,

jagan.