Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
HI All,
How to get max date record..
ID | Date | sales |
100 | 1/1/2014 | 1000 |
100 | 1/15/2014 | 2500 |
100 | 2/2/2015 | 100 |
101 | 2/3/2015 | 890 |
101 | 5/13/2015 | 259 |
102 | 5/15/2015 | 500 |
102 | 5/16/2015 | 90 |
My output should be..
ID | Date | sales |
100 | 2/2/2015 | 100 |
101 | 5/13/2015 | 259 |
102 | 5/16/2015 | 90 |
Thanks
Maybe like
...
Left JOIN (YourTable)
LOAD ID,
Date(Max(Date)) as Date,
1 as Flag
RESIDENT YourTable
GROUP BY ID;
RESULT:
LOAD ID, Date, Sum(sales) as sales
RESIDENT YourTable
WHERE Flag =1
GROUP BY ID, Date;
DROP TABLE YourTable;
Create the Straight Table
Dimension : ID
Expression :
Date : FirstSortedValue(Date, -Date)
Sales : FirstSortedValue(Sales, -Date)
If you want a script solution:
LOAD ID,
LastValue(Date) as Date,
LastValue(Sales)
RESIDENT YourTable
GROUP BY ID
ORDER BY ID, Date asc;
DROP TABLE YourTable;
plz chk
Thanks for Quick response....
Suppose some times i have data like this..
ID | Date | sales |
100 | 1/1/2014 | 1000 |
100 | 2/2/2015 | 2500 |
100 | 2/2/2015 | 100 |
101 | 2/3/2015 | 890 |
101 | 5/13/2015 | 259 |
102 | 5/15/2015 | 500 |
102 | 5/16/2015 | 90 |
My output should be..
ID | Date | sales |
100 | 2/2/2015 | 2600 |
101 | 5/13/2015 | 259 |
102 | 5/16/2015 | 90 |
Thanks...
Maybe like
...
Left JOIN (YourTable)
LOAD ID,
Date(Max(Date)) as Date,
1 as Flag
RESIDENT YourTable
GROUP BY ID;
RESULT:
LOAD ID, Date, Sum(sales) as sales
RESIDENT YourTable
WHERE Flag =1
GROUP BY ID, Date;
DROP TABLE YourTable;
Thank You