topic Re: How to select a specific row in QlikView

How to select a specific row

Tue, 25 Feb 2014 22:38:00 GMT

I have a table that has multiple dates for a given record and I would like to select the row with the latest date in the load script. Can I use the peek function to achieve this? I need some help with the syntax.

Thanks,

Smitha.

Re: How to select a specific row

Tue, 25 Feb 2014 22:53:13 GMT

Hi Smitha

FirstSortedValue(RecordID, -date_column) in load script will select the lastest value of date_column per RecordID. Note "-" before date_column to return last instead of first value

Lukasz

Re: How to select a specific row

jagan — Wed, 26 Feb 2014 02:32:40 GMT

Hi,

Try like this

Data:

LOAD

WHERE Flag = 1;

LOAD

RecordNumber,

Date,

If(Previous(RecordNumber) <> RecordNumber, 1, 0) AS Flag

FROM DataSource

ORDER BY RecordNumber, Date;

Hope this helps you.

Regards,

Jagan.

Re: How to select a specific row

Anonymous — Wed, 26 Feb 2014 04:36:15 GMT

Hi Smitha,

Are you trying to obtain the latest date for a specific or for each record? You may be able to try something like this:

Table_MaxDate:

Load

Field,

Max(Date) AS MaxDate

Resident Table

Group By Field;

This resulting table will give you the max date for each record. Note, if you're using an aggregation function like Max() that you'll need to group by any field in your load that you are not aggregating.

Re: Re: How to select a specific row

rwunderlich — Wed, 26 Feb 2014 04:50:22 GMT

You've got a few answers, I'll throw mine in.

Final:

LOAD

RecordID,

Max(Date) AS Date

From Source

Group By RecordID;

INNER JOIN(Final)

LOAD *

FROM Source;

-Rob

http://masterssummit.com

Re: How to select a specific row

hic — Wed, 26 Feb 2014 07:04:49 GMT

... and I would do exactly the same as Rob.

HIC

Re: Re: How to select a specific row

Wed, 26 Feb 2014 14:07:46 GMT

Thanks for all the suggestions.

I tried Rob's method and it worked perfectly! Thank you!