Load script to determine end dates for IntervalMatch
Attached is an example qvw of my situation. For each ID, there is State and state timestamp, which is the start of that state. What I want is a field of the end timestamp for that state, which is at the next state timestamp or the MaxDate if there is no state transition (the last state). This field would then be used with the IntervalMatch to get a table that contains the state at that particular day (StateDays). An example of what I mean is for ID = 1, State A end timestamp would be 11/4/2014 6:03:10 PM (probably 11/4/2014 6:03:09 PM so that is there is no confusion between end of State A and start of State B).
Any help would be great.
Thanks in advance
BTW, the real qvd has ~50,000 IDs, but reload is only every 4 hours.