Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
umartareen
Creator II
Creator II

To Bring Values Only For Minimum Dates

Hi,

I have an issue where I need to bring the field values only for the minimum dates. ie, values with multiple associated dates have to show only the minimum date and the rest need to be eliminated.

As seen in the image below, the segment "0248-002-TIA" appears for both the dates "25-1-2016" and "12-7-2016". This needs to be limited to only the row for date "25-1-2016" .  The Date "12-7-2016" should be eliminated.

Capture.PNG

This can be achieved through front end expression like " only({<Date = {'$(=min(Date))'}>} Segment) " , but I have to achieve this through script.

Help will be much appreciated !

Thanks,

Umar

1 Solution

Accepted Solutions
saimahasan
Partner - Creator III
Partner - Creator III

You can use this

A:

LOAD * INLINE [

    date, Segments

    25-01-2016, 0248-002-TIA

    12-07-2016, 0248-002-TIA

];

Left Join(A)

LOAD Date(min(date)) as Newdate,

  Segments

Resident A

Group By Segments;

B:

LOAD date,

  Segments

Resident A

Where date = Newdate;

DROP Table A;

View solution in original post

3 Replies
saimahasan
Partner - Creator III
Partner - Creator III

You can use this

A:

LOAD * INLINE [

    date, Segments

    25-01-2016, 0248-002-TIA

    12-07-2016, 0248-002-TIA

];

Left Join(A)

LOAD Date(min(date)) as Newdate,

  Segments

Resident A

Group By Segments;

B:

LOAD date,

  Segments

Resident A

Where date = Newdate;

DROP Table A;

umartareen
Creator II
Creator II
Author

Perfect ! Thank You.

Thanks

Umar

swuehl
MVP
MVP

Alternatively, you can use a single LOAD like

SET DateFormat = 'DD-MM-YYYY';

LOAD Segments, Date(Min(date)) as date

INLINE [

    date, Segments

    25-01-2016, 0248-002-TIA

    12-07-2016, 0248-002-TIA

]

GROUP BY Segments;

or have a look at

Get record holds the highest number

for how to filter records with more than the two fields.