Getting the latest records for each day based on a Counter/Timestamp

[#1]

Hi,

I'm having a table like this:

I want to create a new table in the script, which only contains the records for the highest Counter pr Date pr Item.

The result should be:

[sunny_talwar]

Try this:

Set DateFormat='DD.MM.YYYY';

T1:

LOAD Item, Value, Date(Day) as Date, Counter

INLINE [

    Item, Value, Day, Counter

    1, 5, 01.02.2016, 100

    1, 6, 01.02.2016, 101

    2, 5, 01.02.2016, 102

    1, 7, 01.02.2016, 103

    2, 4, 01.02.2016, 104

    1, 6, 02.02.2016, 105

    1, 4, 02.02.2016, 106

];

Right Join(T1)

LOAD Item,

  Date,

  Max(Counter) as Counter

Resident T1

Group By Item, Date;

[maxgro · Accepted solution]

Set DateFormat='DD.MM.YYYY';

T1:

LOAD Item, Value, Date(Day) as Date, Counter

INLINE [

    Item, Value, Day, Counter

    1, 5, 01.02.2016, 100

    1, 6, 01.02.2016, 101

    2, 5, 01.02.2016, 102

    1, 7, 01.02.2016, 103

    2, 4, 01.02.2016, 104

    1, 6, 02.02.2016, 105

    1, 4, 02.02.2016, 106

];

NoConcatenate LOAD * Resident T1

Where Item & '|' & Date <> Peek('Item') & '|' & Peek('Date')

Order By Item, Date, Counter desc;

DROP Table T1;