Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Only show most recent of multiple entry

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!

5 Replies
Not applicable

Only show most recent of multiple entry

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.

Not applicable

Only show most recent of multiple entry

Thanks for the response!

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

QV Load Error.jpg

Not applicable

Only show most recent of multiple entry

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
Valued Contributor III

Re: Only show most recent of multiple entry

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

Hope this helps.

Not applicable

Only show most recent of multiple entry

Thanks again for the responses!

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

Thanks!

Community Browser