To Bring Values Only For Minimum Dates

[umartareen]

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.

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

[saimahasan · Accepted solution]

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]

Perfect ! Thank You.

Thanks

Umar

[swuehl]

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.