CODEWITHSUNDEEP
azure-data-explorerkql
david
david

Reputation: 115

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),
]

Upvotes: 1

Views: 4905

Answers (1)

Yoni L.
Yoni L.

Reputation: 25995

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 |

Upvotes: 3

Related Questions