Remove duplicates keeping the latest in past 3 days

[#1]

Hello,

I would like to load the latest data in past 3 days based on ColA, ColB.

Like, if ColA and ColB repeats in past 3 days, I should load the last record as in the example below:

ID             Date                                    ColA                  ColB        Measure

1   1st July 2016 01:00:00                      M1                      X1            10

2   1st July 2016 02:00:00                      M3                      X1            15

3   1st July 2016 02:30:00                      M1                      X3            20

4   1st July 2016 04:00:00                      M1                      X1            30

5   3rd July 2016 01:00:00                      M1                      X1            05

Expected to load

ID             Date                                    ColA                  ColB        Measure

2   1st July 2016 02:00:00                      M3                      X1            15

3   1st July 2016 02:30:00                      M1                      X3            20

5   3rd July 2016 01:00:00                      M1                      X1            05

Since, M1, X1 combination is repeated in last 3 days, the latest row shoukd only be loaded.

Please Suggest.

Thank you,

Deepa

[sunny_talwar]

Try this:

Table:

LOAD *,

  Date(Floor(Date)) as DateField;

LOAD * INLINE [

    ID,            Date,                                    ColA,                  ColB,        Measure

    1,  1 Jul 2016 01:00:00,                      M1,                      X1,            10

    2,  1 Jul 2016 02:00:00,                      M3,                      X1,            15

    3,  1 Jul 2016 02:30:00,                      M1,                      X3,            20

    4,  1 Jul 2016 04:00:00,                      M1,                      X1,            30

    5,  3 Jul 2016 01:00:00,                      M1,                      X1,            05

];

NewTable:

LOAD *

Where Flag = 0;

LOAD *,

  If(ColA = Previous(ColA) and ColB = Previous(ColB) and Previous(DateField) - DateField <= 3, 1, 0) as Flag

Resident Table

Order By DateField Desc, ColA, ColB;

DROP Table Table;