let val1= toscalar(table1| where id== '123' | project startDate|top 1 by startDate);
let val2= toscalar(table2| where id== '123' | project endDate|top 1 by endDate);

let result= iif( val2!= '' , val2-val1,val1);

let tempTable= datatable(dateValue: string )[
   result
];   //error: The incomplete fragment is unexpected.
tempTable;

So what I am trying to do is this: I have a Table3 where there is a Duration field. It may or may not be null. If it is null, then I need to calculate it myself with the "result" as the value. as seen above.

What I am plannign to do is to calculate the value and then create a tempTable that I will JOIN with Table3. The resulting data will have a new column called dateValue and I can perform further logic to use that value there or not. When I am creating the table tempTable, I get the error "The incomplete fragment is unexpected.". I am not sure what I am missing here? Ultimately I just want to have a table to join with the calculated results.

Also another question... IF my tempTable has the same column name as Table3, how do i use my tempTable to join Table3 and replace the value ONLY if the column in Table3 has empty values? Ex: tempTable - 2,3,4,5 <- values from column Duration Table3 - 3,null,3,null <- values from column Duration. I want result to be 3,3,3,5. Will a join automatically reject the values from tempTable if Table3 has a value there already?

TLDR: want to create a temptable with calculated data and join with main table

let val1= toscalar(table1| where id== '123' | project startDate|top 1 by startDate);
let val2= toscalar(table2| where id== '123' | project endDate|top 1 by endDate);

let result= iif( val2!= '' , val2-val1,val1);

let tempTable= datatable(dateValue: string )[
   result
];   //error: The incomplete fragment is unexpected.
tempTable;

So what I am trying to do is this: I have a Table3 where there is a Duration field. It may or may not be null. If it is null, then I need to calculate it myself with the "result" as the value. as seen above.

What I am plannign to do is to calculate the value and then create a tempTable that I will JOIN with Table3. The resulting data will have a new column called dateValue and I can perform further logic to use that value there or not. When I am creating the table tempTable, I get the error "The incomplete fragment is unexpected.". I am not sure what I am missing here? Ultimately I just want to have a table to join with the calculated results.

Also another question... IF my tempTable has the same column name as Table3, how do i use my tempTable to join Table3 and replace the value ONLY if the column in Table3 has empty values? Ex: tempTable - 2,3,4,5 <- values from column Duration Table3 - 3,null,3,null <- values from column Duration. I want result to be 3,3,3,5. Will a join automatically reject the values from tempTable if Table3 has a value there already?

TLDR: want to create a temptable with calculated data and join with main table

• azure
• kql

• Can you explain clearly, on what you want to do? Also provide sample tables(table1 and table2) with details and expected output. – RithwikBojja Commented Mar 17 at 6:46
• What I want to do is this. I have a Table with a Column that has calculated data like a "duration" that will ONLY be calculated when the user closes the "ticket". When the "ticket" is still open, that data is empty. Ultimately, if I am the user and I open the ticket, I want to see the "Duration" of the ticket. Since if it is empty, I need to calculate it by using data from another table and then Join with the first table to fill that empty value. If ticket is OPEN, Table1's duration column for ticket ID 1, is "null", I calculate it to be "3 days" and need to join and put that into Table 1. – shaselai Commented Mar 18 at 18:16
• I have calculated the value but I am having trouble figuring out how to join them. Ex: Table 1 ID1 Duration column is null. I need to create table 2 with just ID1 and CalculatedDuration to join and the resulting table SHOULD BE Table 1 ID1 Duration "3 days". Ex: Table1 ID2 duration column is 4 days. I can either calculate the value then join and have the 4days value in Table1 stay intact. OR if there's a way to determine I DONT NEED TO JOIN because Table1 ID2 already has data. Maybe IFF? – shaselai Commented Mar 18 at 18:17
• Can you provide sample input table and expected output table? Without this it is difficult to understand the issue – RithwikBojja Commented Mar 19 at 3:14
• Table 1: ID Duration 1 (null) 2 3 days 3 (null) 4 5 days Table 2 ID StartTime LastVisitTime 1 3/1 3/6 2 3/4 3/7 3 3/5 3/10 4 3/2 3/7 query will find only ID1 let val1= toscalar(table2| where id== '1' project StartTime; let val2= toscalar(table2| where id== '1' project LastVisitTime; let duration = val2-val1; I want to create duration as a table to join table1 so table1: ID Duration 1 5 days Note i am only joining one result – shaselai Commented Mar 19 at 22:03

1 Answer 1

I have tried the above scenario and you can see I got the same error.

Here, you are trying to toscalar() function values in the table directly. toscalar() will only give the constant value and to join, it needs a table. That might be the reason for the above error.

You can try the below code where I took some sample data as starting data. Here, it creates the temporary table using datetime_diff. After that, it joins the temporary table and uses coalesce to fill the Duration column. You can replace the table names with yours.

let table1 = datatable(ID:int, Duration: long) [
    1, long(null),
    2, 3,
    3, long(null),
    4, 5
];

let table2 = datatable(ID:int, StartTime:datetime, LastVisitTime:datetime) [
    1, datetime(2024-03-01), datetime(2024-03-06),
    2, datetime(2024-03-04), datetime(2024-03-07),
    3, datetime(2024-03-05), datetime(2024-03-10),
    4, datetime(2024-03-02), datetime(2024-03-07)
];

// Create the temporary table with below query
let TempTable = 
table2
| extend DurationCalculated = datetime_diff('day', LastVisitTime, StartTime)
| project ID, DurationCalculated;

// Use coalesce after joining the temporary table to get the first non-null value and store it in the same column
table1
| join kind=leftouter TempTable on ID
| extend Duration = coalesce(Duration, tolong(DurationCalculated))  // Ensure type match
| project ID, Duration

Result: