kqlazure-data-explorer

Kusto Query replace the timestamp of duplicate rows

I have following have the table with two rows per cart_id. for each second row I want to replace Trolley_Start timestamp with the Begin_Picking timestamp and for each first row I want to replace Trolley_End timestamp with the End_Picking timestamp using Kusto Query language. Some guidance or help will be appreciated.

My Table is look like this: enter image description here

I wanted this to be like this: enter image description here

New to KQL Here is what I tried, but it is not an ideal solution since it is not feasible to pass static IDs for thousands of records

datatable (cart_id: int, id: int, Associate_1: int, Associate_2: int, Trolley_Start: datetime, Begin_Picking: datetime, End_Picking: datetime, Trolley_End: datetime)
[
1, 111, 111, 112, datetime(2024-08-13T09:35:15.991Z), datetime(2024-08-13T09:37:20.123Z), datetime(2024-08-13T10:10:45.789Z), datetime(2024-08-13T10:31:12.345Z),
1, 112, 111, 112, datetime(2024-08-13T09:35:15.991Z), datetime(2024-08-13T10:14:18.567Z), datetime(2024-08-13T10:31:50.234Z), datetime(2024-08-13T10:31:12.345Z),
2, 113, 113, 114, datetime(2024-08-11T14:13:05.678Z), datetime(2024-08-11T14:15:09.456Z), datetime(2024-08-11T14:20:30.789Z), datetime(2024-08-11T16:01:11.001Z),
2, 114, 113, 114, datetime(2024-08-11T14:13:05.678Z), datetime(2024-08-11T14:33:45.123Z), datetime(2024-08-11T16:00:55.456Z), datetime(2024-08-11T16:01:11.001Z),
3, 115, 115, 116, datetime(2024-08-12T06:22:14.333Z), datetime(2024-08-12T06:23:20.111Z), datetime(2024-08-12T09:03:05.789Z), datetime(2024-08-12T07:36:14.678Z),
3, 116, 115, 116, datetime(2024-08-12T06:22:14.333Z), datetime(2024-08-12T07:32:18.789Z), datetime(2024-08-12T07:34:12.123Z), datetime(2024-08-12T07:36:14.678Z),
4, 111, 111, 117, datetime(2024-08-13T10:15:45.789Z), datetime(2024-08-13T10:18:45.789Z), datetime(2024-08-13T10:25:45.789Z), datetime(2024-08-13T10:40:45.789Z),
4, 117, 111, 117, datetime(2024-08-13T10:15:45.789Z), datetime(2024-08-13T10:26:45.789Z), datetime(2024-08-13T10:38:45.789Z), datetime(2024-08-13T10:40:45.789Z)
]

Solution

  • Try with the below code to get your expected output:

    datatable (cart_id: int, id: int, Associate_1: int, Associate_2: int, Trolley_Start: datetime, Begin_Picking: datetime, End_Picking: datetime, Trolley_End: datetime)
[
    1, 111, 111, 112, datetime(2024-08-13T09:35:15.991Z), datetime(2024-08-13T09:37:20.123Z), datetime(2024-08-13T10:10:45.789Z), datetime(2024-08-13T10:31:12.345Z),
    1, 112, 111, 112, datetime(2024-08-13T09:35:15.991Z), datetime(2024-08-13T10:14:18.567Z), datetime(2024-08-13T10:31:50.234Z), datetime(2024-08-13T10:31:12.345Z),
    2, 113, 113, 114, datetime(2024-08-11T14:13:05.678Z), datetime(2024-08-11T14:15:09.456Z), datetime(2024-08-11T14:20:30.789Z), datetime(2024-08-11T16:01:11.001Z),
    2, 114, 113, 114, datetime(2024-08-11T14:13:05.678Z), datetime(2024-08-11T14:33:45.123Z), datetime(2024-08-11T16:00:55.456Z), datetime(2024-08-11T16:01:11.001Z),
    3, 115, 115, 116, datetime(2024-08-12T06:22:14.333Z), datetime(2024-08-12T06:23:20.111Z), datetime(2024-08-12T09:03:05.789Z), datetime(2024-08-12T07:36:14.678Z),
    3, 116, 115, 116, datetime(2024-08-12T06:22:14.333Z), datetime(2024-08-12T07:32:18.789Z), datetime(2024-08-12T07:34:12.123Z), datetime(2024-08-12T07:36:14.678Z)
]
| partition hint.strategy=native by cart_id
(
    order by id asc
    | serialize row_num = row_number()
)
| extend 
    Trolley_Start = iff(row_num == 1, Begin_Picking, Trolley_Start),
    Trolley_End = iff(row_num == 2, End_Picking, Trolley_End)
| project cart_id, id, Associate_1, Associate_2, Trolley_Start, Begin_Picking, End_Picking, Trolley_End
| order by cart_id, id

    Output:

    enter image description here