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

[mike_mascitti]

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!

[amit_kothari · Accepted solution]

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.

[mike_mascitti]

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

[mike_mascitti]

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. 

[mike_mascitti]

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