How to select a specific row

[#1]

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.

[#2]

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

[jagan]

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.

[Anonymous]

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.

[rwunderlich · Accepted solution]

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

[hic]

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

HIC

[#7]

Thanks for all the suggestions.

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