- SAP Managed Tags
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.
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
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.
- 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.
- 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.
- Next I sorted my table by ID and ProcessDate descending since I want the most recent ProcessDate for each ID.
- 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
Bluesky| User | Count |
|---|---|
| 27 | |
| 24 | |
| 13 | |
| 12 | |
| 11 | |
| 11 | |
| 10 | |
| 10 | |
| 9 | |
| 9 |
