Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Qlickers,
I have a doubt on max & min date
My data is like
ID | Date |
4073 | 25-07-2013 |
4073 | 03-04-2014 |
4073 | 01-08-2014 |
17 | 14-04-2012 |
17 | 13-04-2014 |
17 | 01-08-2012 |
from this data I want to find the min & max date based on the ID
Ex:
case 1:
when I select the ID as 4073 ,min date:25-07-2013 ,Max date:01-08-2014
please help me .........
hi
load
ID,
max(Date) as maxdate,
min(Date) as minDate
From tablename
group by ID;
hi
load
ID,
max(Date) as maxdate,
min(Date) as minDate
From tablename
group by ID;
Hi,
Try it in script
Like
Load max(date) as maxDate
min(date) as minDate,
ID
From tableName
Group By ID;
Regards
hi
Tab1:
LOAD * INLINE [
ID, Date
4073, 25-07-2013
4073, 03-04-2014
4073, 01-08-2014
17, 14-04-2012
17, 13-04-2014
17, 01-08-2012
];
LOAD
ID,
max(date(date#(Date,'DD-MM-YYYY'),'DD-MM-YYYY')) as maxdate,
min(date(date#(Date,'DD-MM-YYYY'),'DD-MM-YYYY')) as mindate
Resident Tab1
Group By ID;
DROP Table Tab1;
hi
you can also try according to this
LOAD
ID,
FirstSortedValue(date(date#(Date,'DD-MM-YYYY'),'DD-MM-YYYY'),-date(date#(Date,'DD-MM-YYYY'),'DD-MM-YYYY')) as maxdate,
FirstSortedValue(date(date#(Date,'DD-MM-YYYY'),'DD-MM-YYYY'),date(date#(Date,'DD-MM-YYYY'),'DD-MM-YYYY')) as mindate
Group by ID;
Tab1:
LOAD * INLINE [
ID, Date
4073, 25-07-2013
4073, 03-04-2014
4073, 01-08-2014
17, 14-04-2012
17, 13-04-2014
17, 01-08-2012
];
Check this
Hi All,
Thanks for your valuable replies.........