topic Re: Best way to show Most Recent row in a Business Schema? in Dashboards & Analytics Discussions

Best way to show Most Recent row in a Business Schema?

mike_mascitti — Thu, 08 Sep 2022 19:46:07 GMT

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!

Re: Best way to show Most Recent row in a Business Schema?

amit_kothari — Thu, 08 Sep 2022 22:56:56 GMT

Hi Mike - you can use the 'Last Version' operator as a filter in your insight. Just drag ID into the filter pill and then drag a Effective Date (should be Date datatype ) field into the Values field . Check the attached screenshot.

Re: Best way to show Most Recent row in a Business Schema?

mike_mascitti — Fri, 16 Sep 2022 14:13:45 GMT

Thanks, Amit, this is just what we were looking for!

Re: Best way to show Most Recent row in a Business Schema?

mike_mascitti — Fri, 11 Nov 2022 13:49:54 GMT

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

Re: Best way to show Most Recent row in a Business Schema?

mike_mascitti — Thu, 15 Dec 2022 20:07:51 GMT

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?