Point in time analysis:
Hey Folks,
I have an issue that is similar to this forum issue but not quite:
http://community.qlik.com/forums/t/31679.aspx
So, I'm starting a new thread.
In my database, I keep full copies of historic records. Whenever a record in my system is updated, a full copy of the old record remains in a log table, so I can come back and see what my records looked like at a point in time. This log table contains all of the detail fields from the record, and an 'update date'.
In the database, I'd go to this log table, and look for all of the updates where the update date is earlier than my selected date, and identify just the one record that was closest to the date I'm interested in.
Ie: if I want to know what a record looked like on Jan 1 2010, I'd look into the table for all updates prior to that date, and select the latest one to look at.
I'm struggling with how to do this in QV, and I'm beginning to think that I've been looking at this too long, and over-engineering my solution attempts; I'd love some suggestions.
I've tried things like creating a straight table, using formulas like this:
=only({<"$=date(RS_EDIT_DATE)"={"$(=date(max(RS_EDIT_DATE)))"}>}MYDATAFIELD)
But, in the set, max(date) is interpreted as the max for the entire table, not just the max of the record being discussed in the row. So, in my table, I get only one record.
I've tried using 'IF' statements:
IF(RS_EDIT_DATE = max(RS_EDIT_DATE),MYDATAFIELD)
But, when this is the only field in the table the results seem a bit unpredictable. (I can go into detail, but I don't think it's relevant, but needless to say, are not what I want)
I've tried a couple of other things, without success.
Creating duplicate records for every one of my master records for every passing day on a master calendar seems needlessly inefficient, and totally wasteful… it would work, but I'm not going to do it.
Here's some simple code that generates a really simple example of what I'm working with:
LOAD * INLINE [
Record ID, MyDataField, Update Date
1, Happy, 1
1, Sad, 21
1, 0, 44
2, 43, 1
2, Apple, 12
2, Hippopotamus, 27
];
And yet another explanation of what I expect, using the above data:
I want to know what all of my records looked like on date = 300
I look into my table, I have two records, (1 and 2)
The largest update dates that are smaller than 300 are 44, and 27 respectively, so:
For 1, I would expect to see '0'
For 2, I would expect to see 'Hippopotamus'
I try again with date = 14.
Same procedure, I expect to see:
For 1: 'Happy'
For 2: 'Apple'
I hope that's clear.
Assistance appreciated!
