How to Join two KQL tables with between operators (instead of ON)

Question

I'm trying to join two KQL tables with between operator as you can see below:

let Table1 = datatable(ProductID:int,ProductName:string,Price:real) [ 1, "Laptop", 1000.0, 2, "Smartphone", 500.0,3, "Tablet", 700.0 ];

let Table2 = datatable(SaleID:int,ProductID:int,Timestamp:datetime) [ 101, 1, datetime(2024-06-10T08:00:00Z), 102, 2, datetime(2024-06-11T10:30:00Z), 103, 3, datetime(2024-06-11T11:45:00Z)];

Table1 | join kind=inner (Table2) on $right.Timestamp between ($left.Timestamp .. $left.Timestamp) | project-away ProductID1,ProductID

I'm encountering an error message indicating that the join attributes must either be individual column names or expressions for equality. Exact error message you can find below:

Can you please help me to resolve this issue?

I'm trying to join two KQL table with help of between operator instead of ON operator.

Table1 doesn't have timestamp colun itself. Could you share the correct input data — Aswin
– Aswin, Commented Jun 11, 2024 at 5:34
check if the solution below helped you. Let me know if there's anything else I can assist with — Aswin
– Aswin, Commented Jul 5, 2024 at 12:24

Aswin · Accepted Answer · 2024-06-11 07:49:10Z

Your Table1 doesn't have timestamp columns. I have taken sample timestamp columns like createdTime and UpdatedTime in Table1. In order to join two tables whose timestamp is between CreatedTime and updatedTime, you cannot use between operator in join matching condition. Only equi-join is supported here. The workaround approach is as below.

let window=1h;
let Table1 = datatable(ProductID:int,ProductName:string,Price:real,CreatedTime:datetime,UpdatedTime:datetime) [ 
  1, "Laptop", 1000.0, datetime(2022-01-01T00:00:00Z), datetime(2022-01-02T00:00:00Z),
  2, "Smartphone", 500.0, datetime(2022-01-01T00:00:00Z), datetime(2022-01-02T00:00:00Z),
  3, "Tablet", 700.0, datetime(2022-01-01T00:00:00Z), datetime(2022-01-02T00:00:00Z)
];
let Table2 = datatable(SaleID:int,ProductID:int,Timestamp:datetime) [ 
  101, 1, datetime(2022-01-01T01:00:00Z), 
  102, 2, datetime(2022-01-01T02:00:00Z), 
  103, 3, datetime(2022-01-01T03:00:00Z)
];
Table1
| extend Timestamp = range(bin(CreatedTime, window), bin(UpdatedTime,window), window)
| mv-expand Timestamp to typeof(datetime)
| join kind=inner (Table2)
on $left.Timestamp ==$right.Timestamp and  $left.ProductID ==$right.ProductID
| project-away ProductID1,ProductID

Using Range operator, all the value between createdTime and UpdatedTime are computed with the interval of 1h window. (change it as per requirement). Then using mv-expand expand the dynamic values into multiple rows. Join the resultant table with table2 on matching productId and timestamp. This way, you can achieve the same requirement.

Collectives™ on Stack Overflow

How to Join two KQL tables with between operators (instead of ON)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related