Skip to Content
Technical Articles

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

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

 

5 Comments
You must be Logged on to comment or reply to a post.
  • Good one Noel..But this you can achieve with First/Last and Rank functions also 🙂

    measure objects also we can hide in BI 4.2 SP05 but not sure exactly from which support pack this is supported..i have tested only in BI 4.2 SP05.

    • I tried using Rank, but it gave me the ID for the overall maximum ProcessDate. I want an instance of each ID and corresponding data associated with the maximum ProcessDate for each ID where theire maximum ProcessDates are different. I also looked briefly at First/Last, but didn’t see how they would work for my use.

      I will investigate more tomorrow.

      Thanks!

      • Using Max function:

        1. Find Max date=Max([Date]) In ([ID])
        2. V max Show/Hide=If([Date]=Max([Date]) In ([ID])) Then “Show” Else “Hide”
        3. Filter on V max Show/Hide where equal to “Show

         

        Using Last function:

        1. Sort ID and Date in ascending order.
        2. Find last date by ID=Last([Date]) In ([ID])
        3. V Last Show/Hide=If([Date]=Last([Date]) In ([ID])) Then “Show” Else “Hide”
        4. Filter on V Last Show/Hide where equal to “Show

        • I really like your solution. I think I will use your approach instead.

          Hopefully this will help someone else down the road too.

          Thanks,

          Noel