Get First (or Last) Record for ID in Web Intelligence

[nscheaffer]

I have a table that contains monthly snapshots of data. So one ID (e.g. Product) will have exactly one instance for each month marked by a ProcessDate which is the last business day of the month.

How do I go about getting the first or last record for each ID and its corresponding data (Quantity) at that point in time based on a ProcessDate with the understanding the most recent ProcessDate could vary from one ID to another. Therefore, I cannot use the maximum ProcessDate overall.

First I'll explain two possible approaches in SQL Server which led me to figuring out how to acheive this in Web Intelligence using a universe.

In SQL I can find the maximum ProcessDate per ID by using a subquery and then join back to my table. Another approach is Using the OVER clause with the ROW_NUMBER function.

This db fiddle shows each of these in action. Note I am using European date format since that is what db fiddle uses. Here is a screenshot if you cannot get to that link...



Within Web Intelligence I had tried various things such as Ranking, but as I understand it you need to base it on a measure. I want to base my rank on whatever the maximum ProcessDate (not a measure) is for a given ID. I finally came up with a workable solution similar to the second SQL concept from above.

1. I created my query with the data I wanted from my universe. Here are my results which should look familiar based on my db fiddle example.
   


2. Then I created a variable named ID Row Number using the RunningCount function to mimic what the OVER clause with the ROW_NUMBER function is doing in SQL Server and added it to my table.


3. Next I sorted my table by ID and ProcessDate descending since I want the most recent ProcessDate for each ID.
   


4. Finally, I put a filter on my table to only show those rows with a ID Row Number = 1.

Done!

The one drawback to this is that since ID Row Number is a measure I cannot hide it, but I can live with that. I would be happy to hear your suggestions of how this sort of thing can be improved upon.

Enjoy!

Noel