Retrieving last known record (based on a date) in series of logged events
Given a log file with these records representing, hypotethically, the ‘movements’ or changes in residential city, for citizens, like this:
ID CitizenRef RelocationDate City Surname
48 1000 2015-04-01 Boston Smith
49 1000 2015-07-01 Seattle Smith
50 1000 2015-10-01 Boston Smith
51 1000 2016-01-15 NewYork Smith
Note - Smith (1000) lived in Boston, Seattle, and again in Boston (2015) and finally, in NewYork (2016). He has multiple entries in this log.
I want to perform analytics based on the citizens that are currently living in NewYork. To do this, I created a dimension ‘IsLastKnownRecord’, which is populated with the value “True” when processing a row that I know represents the last known movement for the citizen, and “False” otherwise. The sourceExpression for this dimension calls a query on the source table to determine this.
This way, to perform analytics based on a list of citizens that are currently in NewYork, (or other city for that matter) I merely have to include a filter where IsLastKnownRecord=”true”.
This seems to work, but I’m wondering if there is another (more elegant ?) way of achieving the same result without creating a the dimension ‘IsLastKnownRecord’ in the cube, but instead, directly as part of the pivot table.