Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Load all records where they ever existed in a specific state

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

Capture.PNG

Please see attached and thanks very much.

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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';


talk is cheap, supply exceeds demand

View solution in original post

2 Replies
Gysbert_Wassenaar

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';


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

Thank you!