Skip to Content
Technical Articles
Author's profile photo Noel Scheaffer
Noel Scheaffer
November 1, 2018 2 minute read

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

 

Assigned Tags

      5 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo AMIT KUMAR
      AMIT KUMAR

      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.

      Author's profile photo Noel Scheaffer
      Noel Scheaffer
      Blog Post Author

      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!

      Author's profile photo AMIT KUMAR
      AMIT KUMAR

      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

      Author's profile photo Noel Scheaffer
      Noel Scheaffer
      Blog Post Author

      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

      Author's profile photo Manikandan Elumalai
      Manikandan Elumalai

      Good attempt Noel and Thanks for the solution Amit!