Load all records where they ever existed in a specific state

[Anonymous]

I have a history qvd that tracks changes to a record.  Record_Id 1 for example has 3 history records associated to it, in two of those history records the status was "Escalated" and in the other the status was something other than "Escalated"  I want to load all 3 records for record_id 1 because at some point its status was "escalated"  All I seem to be able to get is ONLY the escalated records.  If a record_id was never in a status "escalated", I do not want to load it all.  In the example below, I would get 6 records returned, all of record_id 1 and 3

Please see attached and thanks very much.

[Gysbert_Wassenaar · Accepted solution]

records:

LOAD * INLINE [

    Record_Id, History_id, Old_Status, New_Status

    1, 1, New, Assigned

    1, 2, Assigned, Escalated

    1, 3, Escalated, Resolved

    2, 1, New, Assigned

    2, 2, Assigned, Resolved

    3, 1, New, Assigned

    3, 2, Assigned, Escalated

    3, 3, Escalated, Resolved

    4, 1, New, Assigned

    4, 2, Assigned, Resolved

    5, 1, New, Assigned

    5, 1, Assigned, Resolved

];

right join

load distinct Record_Id

resident records

where Old_Status = 'Escalated';

[Anonymous]

Thank you!