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
sunny_talwar

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;

Capture.PNG

View solution in original post

1 Reply
sunny_talwar

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;

Capture.PNG