Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
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;