Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

jhlovenc
Contributor II

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
MVP & Luminary
MVP & Luminary

Re: Load all records where they ever existed in a specific state

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
2 Replies
MVP & Luminary
MVP & Luminary

Re: Load all records where they ever existed in a specific state

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
jhlovenc
Contributor II

Re: Load all records where they ever existed in a specific state

Thank you!