Remove unwanted rows in a table

[#1]

Hi,

I have a straight table with content as shown below

CID	LID	Desc	StartDate	EndDate
1	1	Test	30/06/2012	30/06/2013
1	2	Test2	31/10/2013	31/10/2014
2	1	Test	30/11/2008	30/11/2013
2	2	Test2	31/07/2014	31/07/2015
3	1	Test	29/02/2008	28/02/2013
3	2	Test2	31/10/2013	31/10/2014
4	1	Test	31/08/2012	31/08/2013
4	2	Test2	31/08/2014	31/08/2015

I would like to keep only one row for each CID, i.e the latest row of data.

Can someone please let me know how to do this in the front end and not in the script?

Thanks

[krishna_2644]

You can try using FirstSortedValue()  function : FirstSortedValue(CID, StartDate).

let me know if that doesnt work

[#3]

Razor , While leading the data, just load the data what you need.

Data:

LOAD

     CID,

     LID,

     DESC,

     StartDate,

     EndDate,

From Source;

Inner Join(DATA)

LOAD

     CID,

     Max(StartDate) AS StartDate

Resident DATA

Group By CID;

The above script keeps only latest record CID based on StartDate.

[patroser]

You could work with max()

Taking CID, LID, Desc and StartDate as Dimensions and max(EndDate) as expression ends up in a straight table just holding the values of the latest EndDate.

[maxgro · Accepted solution]

[#6]

That's working now. Thanks to everyone, especially Krishna and Massimo