topic Re: To Bring Values Only For Minimum Dates in QlikView

To Bring Values Only For Minimum Dates

umartareen — Sun, 11 Sep 2016 17:03:45 GMT

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

Re: To Bring Values Only For Minimum Dates

saimahasan — Sun, 11 Sep 2016 17:19:34 GMT

You can use this

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;

LOAD date,

Segments

Resident A

Where date = Newdate;

DROP Table A;

Re: To Bring Values Only For Minimum Dates

umartareen — Sun, 11 Sep 2016 17:30:46 GMT

Perfect ! Thank You.

Thanks

Umar

Re: To Bring Values Only For Minimum Dates

swuehl — Sun, 11 Sep 2016 17:33:22 GMT

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.