azure-data-explorerkqlaskql

Need to achieve the below output using Kusto Query language(KQl)

Sample Data. We are expected to receive Products everyday with Total counts.

TimeStamp Product Desc Product Code Available count
2022-01-02T09:00:00Z Berries 111 10
2022-01-02T09:00:00Z Chocolate 222 20
2022-01-02T09:00:00Z Mayo 333 30
2022-01-03T09:00:00Z Berries 111 15
2022-01-03T09:00:00Z Chocolate 222 22
2022-01-04T09:00:00Z Berries 111 30

If no product received on that particular day, i have to show the last date received product as the current day.

TimeStamp Product Desc Product Code Available count
2022-01-03T09:00:00Z Berries 111 15
2022-01-03T09:00:00Z Chocolate 222 22
2022-01-03T09:00:00Z Mayo 333 30
2022-01-04T09:00:00Z Berries 111 30
2022-01-04T09:00:00Z Chocolate 222 22
2022-01-04T09:00:00Z Mayo 333 30
Solution 
  • datatable (['TimeStamp']:datetime,['Product Desc']:string,['Product Code']:int,['Available count']:int)
[
     '2022-01-02T09:00:00Z' ,'Berries'   ,111 ,10
    ,'2022-01-02T09:00:00Z' ,'Chocolate' ,222 ,20
    ,'2022-01-02T09:00:00Z' ,'Mayo'      ,333 ,30
    ,'2022-01-03T09:00:00Z' ,'Berries'   ,111 ,15
    ,'2022-01-03T09:00:00Z' ,'Chocolate' ,222 ,22
    ,'2022-01-04T09:00:00Z' ,'Berries'   ,111 ,30
]
| summarize arg_max(['TimeStamp'], *) by ['Product Code']
    Product Code TimeStamp Product Desc Available count
    333 2022-01-02T09:00:00Z Mayo 30
    222 2022-01-03T09:00:00Z Chocolate 22
    111 2022-01-04T09:00:00Z Berries 30

    Fiddle