09-08-2022 12:46 PM
Hello, we have a few use cases where we need to view a row from a table that's the most recent version of that row. We have a table with rows with an ID and Row Effective Date, and we load incrementally over time. Every now and then we'll get a new row for an ID, but we'll keep both rows on our table so we have history and effective dates for each update. We get updates on IDs at different intervals.
Sometimes we only want the most recent row for an ID. What's the best way to apply a filter to a business schema that basically says 'Greatest Row Effective Date for this ID'
We can build an MV in our schema that lists IDs with their latest effective dates, and then join that with our main table, but I'm hoping there's a simple way to do this on a business schema.
Thanks!
Solved! Go to Solution.
09-08-2022 03:56 PM - edited 09-08-2022 03:56 PM
09-16-2022 07:13 AM
Thanks, Amit, this is just what we were looking for!
12-15-2022 12:07 PM
Hi Amit, moving my 2nd question up here so it hopefully notifies you.
I ran into another similar case but it has a composite key.
However, when I apply the 'Last Version' of each piece of the key the dashboard uses each filter independently. Is there a way to take the Last Version of multiple keys at once?
I could also just use the max(Effective Date) for the report, and it looks like the 'In Query' option might work for that. Can you tell me how to use the In Query operator to return the max value from a column on the dashboard? That should accomplish the same thing.
11-11-2022 05:49 AM
Hi Amit - I ran into another similar case, but it has a composite key.
However, when I apply the 'Last Version' of each piece of the key the dashboard uses each filter independently. Is there a way to take the Last Version of multiple keys at once?
I could also just use the max(Effective Date) for the report, and it looks like the 'In Query' option might work for that. Can you tell me how to use the In Query operator to return the max value from a column on the dashboard? That should accomplish the same thing.
Thank you