Retrieving last known record (based on a date) in series of logged events
Hi,
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.
Thanks
Steve
Hello. You can do it like this:
Thanks, but in my question, when asking 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."
I meant, is there a way of achiving the filtered results showing the last recorded date, using, only MDX syntax - (and not creating a dimension derived from the underlying source data).
Your solution assumes I have access to the source class and data model. If I do, then yes, i'll extend the underlying source data model in one of several ways (including that mentioned in a subsequent post).
Note also - that I still want to quert all logged events so setting a cube buildRestriction is not something I would be wanting to do.
thanks anyway,
Steve
You can calculate such property on a class side, something like this. Index by CitizenRef and RelocationDate, and method which looking for the next date, and returns true if nothing found.
And result you can see below
Good way to calculate such property. One question I have is why you use indirection and $name function in the generated method? It can be safely removed, for example like this:
Just because, I wanted to split getting full index location, and generated code.
What I have done in previous projects is to link the correlated records, e.g.:
ID CitizenRef RelocationDate City Surname NextLocationId
...
48 1000 2015-04-01 Boston Smith 49
49 1000 2015-07-01 Seattle Smith 50
50 1000 2015-10-01 Boston Smith 51
51 1000 2016-01-15 NewYork Smith NULL
You can quickly filter for current locations by setting your filter to "NextLocationId IS NULL". This is not very different from the other comments so far, but this approach is more flexible.
If you ask the question: "Who lived in Boston and relocated to Seattle" you can check for the case (pseudo-filter)
You can even ask more complex questions like "Who relocated to Boston and stayed longer than a year after living in NewYork?"
This is a good idea.
But once again, though, the core question was whether MDX afforded me syntax, that would show only the latest RelocationDates for distinct CitizenRef's found.
(so - assume, my source table only has ID, CitizenRef, RelocationDate, City and Surname fields, and cannot be modified).
Thanks for the suggestion though.
Steve
Hi, Steve!
Have you tried %Last function?
E.g. in a MDX query below it counts records only for the last month of sales for every product.
Social networks
InterSystems resources
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue