Grouping load

[Anonymous]

Hi all,

I have a table below where I woud like to find the nearest DATE from today, but in addition I would like to retrieve the Comment too.

Table_1:

Serial	Date	Comment
A	10/04/2017	OCCURRENCE AA
A	10/05/2017	OCCURRENCE BA
C	16/05/2016	OCCURRENCE AA
C	16/05/2017	OCCURRENCE BA
A	10/01/2017	OCCURRENCE CA
A	10/02/2017	OCCURRENCE DA
B	22/02/2018	OCCURRENCE AB
B	21/05/2017	OCCURRENCE BB
C	15/03/2017	OCCURRENCE AC

For getting the nearest date I have the code below, but unfortunatelly I get an error while trying to get comment.

Table_2:

load

    date(min(if(Date>Today(), Date)))  as NEAREST,

    Serial

resident Table_1 Group by Serial;

The desirable output should be:

Serial	Date	Comment
A	10/04/2017	OCCURRENCE AA
B	21/05/2017	OCCURRENCE BB
C	16/05/2017	OCCURRENCE BA

Does anyone have an idea?

[sunny_talwar · Accepted solution]

May be this:

Table:

LOAD *,

  If(Date - Today() >= 0, Date - Today(), 0) as Days;

LOAD * INLINE [

    Serial, Date, Comment

    A, 10/04/2017, OCCURRENCE AA

    A, 10/05/2017, OCCURRENCE BA

    C, 16/05/2016, OCCURRENCE AA

    C, 16/05/2017, OCCURRENCE BA

    A, 10/01/2017, OCCURRENCE CA

    A, 10/02/2017, OCCURRENCE DA

    B, 22/02/2018, OCCURRENCE AB

    B, 21/05/2017, OCCURRENCE BB

    C, 15/03/2017, OCCURRENCE AC

];

Right Join (Table)

LOAD Serial,

  Min(Days) as Days

Resident Table

Where Days <> 0

Group By Serial;