Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for
Search instead for
Did you mean:
Anonymous
Not applicable

## Grouping load

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:

SerialDateComment
A10/04/2017OCCURRENCE AA
A10/05/2017OCCURRENCE BA
C16/05/2016OCCURRENCE AA
C16/05/2017OCCURRENCE BA
A10/01/2017OCCURRENCE CA
A10/02/2017OCCURRENCE DA
B22/02/2018OCCURRENCE AB
B21/05/2017OCCURRENCE BB
C15/03/2017OCCURRENCE 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:

SerialDateComment
A10/04/2017OCCURRENCE AA
B21/05/2017OCCURRENCE BB
C16/05/2017OCCURRENCE BA

Does anyone have an idea?

1 Solution

Accepted Solutions
MVP

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;

1 Reply
MVP

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;

Community Browser