CODEWITHSUNDEEP
azure-data-explorerkql
Kameshwaran R
Kameshwaran R

Reputation: 25

Fill the empty values with lastknown value in kusto kql

Let example i have one master table

The ID 1, 2 having empty values for X column

ID DateTime IngestionTime X Y Z
1 2012-12-28T12:04:00 2012-12-28T12:04:00 12 11 10
2 2012-12-28T12:06:00 2012-12-28T12:06:00 2 9 7
3 2012-12-29T12:11:00 2012-12-29T12:11:00 2 9 7
1 2012-12-29T12:15:00 2012-12-29T12:15:00 33 7
2 2012-12-29T12:24:00 2012-12-29T12:24:00 9 7

I'm having in function demo(datetime:fromTime, datetime:toTime) from this I'm querying for fromTime 2012-12-29T12:11:00 to toTime: same 29thdecmber) so if any empty values there i need to fill those empty values from previous date with respective column

Need a filled x value for the same ID from the master table

ID DateTime IngestionTime X Y Z
1 2012-12-28T12:04:00 2012-12-28T12:04:00 12 11 10
2 2012-12-28T12:06:00 2012-12-28T12:06:00 2 9 7
3 2012-12-29T12:11:00 2012-12-29T12:11:00 2 9 7
1 2012-12-29T12:15:00 2012-12-29T12:15:00 lastknownvalueforthisID? 33 7
2 2012-12-29T12:24:00 2012-12-29T12:24:00 lastknownvalueforthisID? 9 7

Upvotes: 2

Views: 839

Answers (2)

David דודו Markovitz
David דודו Markovitz

Reputation: 44991

If the gaps are always at the end, you can use the following query.

let t = datatable(ID:int, DateTime:datetime, IngestionTime:datetime, X:int, Y:int, Z:int)
[
    1 ,datetime(2012-12-28T12:04:00) ,datetime(2012-12-28T12:04:00) ,12        ,11 ,10
   ,2 ,datetime(2012-12-28T12:06:00) ,datetime(2012-12-28T12:06:00) ,2         ,9  ,7
   ,3 ,datetime(2012-12-29T12:11:00) ,datetime(2012-12-29T12:11:00) ,2         ,9  ,7
   ,1 ,datetime(2012-12-29T12:15:00) ,datetime(2012-12-29T12:15:00) ,int(null) ,33 ,7
   ,2 ,datetime(2012-12-29T12:24:00) ,datetime(2012-12-29T12:24:00) ,int(null) ,9  ,7
];
let last_notnull_X_values = 
t
| where isnotnull(X)
| summarize arg_max(DateTime, X) by ID
| project ID, new_X = X;
t
| lookup last_notnull_X_values on ID
| extend X = coalesce(X, new_X)
| project-away n`ew_X
ID DateTime IngestionTime X Y Z
1 2012-12-28T12:04:00Z 2012-12-28T12:04:00Z 12 11 10
2 2012-12-28T12:06:00Z 2012-12-28T12:06:00Z 2 9 7
3 2012-12-29T12:11:00Z 2012-12-29T12:11:00Z 2 9 7
1 2012-12-29T12:15:00Z 2012-12-29T12:15:00Z 12 33 7
2 2012-12-29T12:24:00Z 2012-12-29T12:24:00Z 2 9 7

Fidlde

Upvotes: 0

David דודו Markovitz
David דודו Markovitz

Reputation: 44991

datatable(ID:int, DateTime:datetime, IngestionTime:datetime, X:int, Y:int, Z:int)
[
    1 ,datetime(2012-12-28T12:04:00) ,datetime(2012-12-28T12:04:00) ,12        ,11 ,10
   ,2 ,datetime(2012-12-28T12:06:00) ,datetime(2012-12-28T12:06:00) ,2         ,9  ,7
   ,3 ,datetime(2012-12-29T12:11:00) ,datetime(2012-12-29T12:11:00) ,2         ,9  ,7
   ,1 ,datetime(2012-12-29T12:15:00) ,datetime(2012-12-29T12:15:00) ,int(null) ,33 ,7
   ,2 ,datetime(2012-12-29T12:24:00) ,datetime(2012-12-29T12:24:00) ,int(null) ,9  ,7
]
| partition hint.strategy=native by ID
  (
    order by DateTime asc 
    | scan with (step s: true => X = coalesce(X, s.X);)
  )
ID DateTime IngestionTime X Y Z
1 2012-12-28T12:04:00Z 2012-12-28T12:04:00Z 12 11 10
1 2012-12-29T12:15:00Z 2012-12-29T12:15:00Z 12 33 7
3 2012-12-29T12:11:00Z 2012-12-29T12:11:00Z 2 9 7
2 2012-12-28T12:06:00Z 2012-12-28T12:06:00Z 2 9 7
2 2012-12-29T12:24:00Z 2012-12-29T12:24:00Z 2 9 7

Fiddle

Upvotes: 2

Related Questions