Only show most recent of multiple entry

[#1]

Sorry for the poorly worded title but I'm not sure how to explain in one sentence.

I have an enormous amount of package tracking data I'm trying to organize, and I have every update of the package tracking.

Example:

Package1 - 5/19 - 2:30 - picked up

Package1 - 5/19 - 3:30 - in transit

Package1 - 5/20 - 8:02 - departed

Package1 - 5/20 - 9:45 - delivered

Package2 - 5/19 - 2:30 - picked up

Package2 - 5/19 - 3:30 - in transit

Package2 - 5/20 - 8:02 - departed

Package2 - 5/20 - 9:45 - delivered

Package3 - 5/22 - 7:42 - picked up

Package3 - 5/22 - 9:37 - in transit

How do I get may table to only show the most recent update?

Package1 - 5/20 - 9:45 - delivered

Package2 - 5/20 - 9:45 - delivered

Package3 - 5/22 - 9:37 - in transit

I receive file every 2 hours and add this data to a qvd which I then make the table I am asking about.

Thanks!

[#2]

In your load script sort the table in descending order by package, date, time.

package_summary:

Load * from Package

order by PackageID , Date, Time desc

Then add something like this to get the results out of your sorted table:

most_recent:

Load

if(Previous(PackageID) = PackageID, 'Old', 'Most Recent') as RecentFlag,

*

from package_summary;

It is critical that you sort the table in descending order or the previous expression will flag the 1st instance of a package not the last.

[#3]

Thanks for the response!

I added the 'order by' segment to my .qvd LOAD script but I'm getting a 'Garbage after statement' error.

[#4]

Sorry my bad, order by only works on resident tables. Probably more efficient ways to do it but this works...

tracking_temp:

load * from TRACKING_DATA_TEST.qvd;

tracking_temp_2:

Load * resident tracking_temp

order by [TRCK#] desc, LAST_STATUS_UPDATE desc;

drop table tracking_temp;

Tracking:

Load

if(Previous(PackageID) = PackageID, 'Old', 'Current') as CurrentFlag,

*

resident tracking_temp_2;

drop table tracking_temp_2;

[nagaiank]

Here is another working solution. You may modify the script depending on the need.

Hope this helps.

[#6]

Thanks again for the responses!

I was able to piece together a solution with info from both.

Thanks!