Azure Kusto Data Explorer: combine rows by column

I am looking to update/extend column Process such that it reflects whether or not the Process has ended.

For example, table below should return:

Id  Process  
1   Completed
1   Completed
1   Completed   //Id 1: because the last Process state was End 
2   InProgress
2   InProgress  //Id 2: because the last Process state was not End 
3   InProgress
3   InProgress
3   InProgress
3   InProgress  //Id 3: because the last Process state was not End 

Table:

datatable(Id:int, Process:string, UpdateTime: datetime))
[
    1, "Initiate", datetime(2020-02-02 12:00:00),
    1, "Start", datetime(2020-02-02 13:00:00),
    1, "End", datetime(2020-02-02 14:00:00),
    2, "Initiate", datetime(2020-02-02 12:00:00),
    2, "Start", datetime(2020-02-02 13:00:00),
    3  "Initiate", datetime(2020-02-02 12:00:00),
    3, "Start", datetime(2020-02-02 13:00:00),
    3, "End", datetime(2020-02-02 14:00:00),
    3, "Reopen", datetime(2020-02-02 15:00:00),
]

1 answer

  • answered 2020-02-18 18:10 Yoni

    you could try something along the following lines:

    datatable(Id:int, Process:string, UpdateTime: datetime)
    [
        1, "Initiate", datetime(2020-02-02 12:00:00),
        1, "Start", datetime(2020-02-02 13:00:00),
        1, "End", datetime(2020-02-02 14:00:00),
        2, "Initiate", datetime(2020-02-02 12:00:00),
        2, "Start", datetime(2020-02-02 13:00:00),
        3, "Initiate", datetime(2020-02-02 12:00:00),
        3, "Start", datetime(2020-02-02 13:00:00),
        3, "End", datetime(2020-02-02 14:00:00),
        3, "Reopen", datetime(2020-02-02 15:00:00),
    ]
    | order by Id asc, UpdateTime asc
    | extend session_start = row_window_session(UpdateTime, 365d, 365d, Id != prev(Id))
    | as hint.materialized = true T
    | lookup (
        T
        | summarize arg_max(UpdateTime, Process) by session_start, Id
        | project Id, LastProcess = Process
    ) on Id
    | project Id, Process = case(LastProcess == "End", "Completed", "InProgress")
    

    which returns:

    | Id | Process    |
    |----|------------|
    | 1  | Completed  |
    | 1  | Completed  |
    | 1  | Completed  |
    | 2  | InProgress |
    | 2  | InProgress |
    | 3  | InProgress |
    | 3  | InProgress |
    | 3  | InProgress |
    | 3  | InProgress |