Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
Thanks for the response!
I added the 'order by' segment to my .qvd LOAD script but I'm getting a 'Garbage after statement' error.
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;
Here is another working solution. You may modify the script depending on the need.
Hope this helps.
Thanks again for the responses!
I was able to piece together a solution with info from both.
Thanks!