Max Record

[paulwalker]

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

[Kushal_Chawda]

Create the Straight Table

Dimension : ID

Expression :

Date : FirstSortedValue(Date, -Date)

Sales : FirstSortedValue(Sales, -Date)

[swuehl]

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;

[Chanty4u]

plz chk

[paulwalker]

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...

[swuehl · Accepted solution]

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;

[paulwalker]

Thank You