Grouping load

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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:

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:

Does anyone have an idea?

Re: Grouping load

sunny_talwar — Thu, 16 Mar 2017 13:30:20 GMT

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;