Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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';
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';
Thank you!