How to Use To Scalar Function in Kusto | To Scalar Function in Kusto Query Language | KQL Tutorial

Topic: How to Use To Scalar Function in Kusto Query Language.

In this article, we are going to learn about toscalar function, toscalar function returns a scalar constant value of the evaluated expression, Kusto Query Language is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modeling, and more.

 //to_scalar()- Returns a scalar constant value of the evaluated expression.  
   
 //create Table and Insert Sample Data in Azure Data Explorer DB for Testing  
 .drop table TotalSale  
 .create table TotalSale (  
   id: int   
   ,SalePersonFName: string  
   ,SalePersonLName : string  
   ,ProductName : string  
   ,ItemsSold : int  
   ,SoldPrice :real  
   ,SoldDate: date  
   ,City : string  
   ,State : string  
   ,Country : string  
   ,Region : string  
   )  
        

      //Insert data  
       .ingest inline into table TotalSale <|  
 11,Tamara,Tony,Cell Phone,2,1200,2015-03-03,Frankfurt,Hesse,Germany,Europe  
 9,Petra,Henry,TV,10,5000,2015-04-08,Paris,Île-de-France,France,Europe  
 3,Christy,Ladson,TV,3,1600,2015-04-02,High Point,NC,USA,North America  
 7,Chirag,Patel,Cell Phone,5,1500,2015-06-23,AhmadAbad,Gujrat,India,Asia  
 2,M,Raza,Cell Phone,2,800,2015-07-15,Charlotte,NC,USA,North America  
 5,Najaf,Ali,Computer,1,300,2015-06-20,Karachi,Sindh,Pakistan,Asia  
 6,Sukhjeet,Singh,TV,2,900,2015-06-21,ChandiGar,Punjab,India,Asia  
 4,John,Rivers,Laptop,5,2400,2014-03-09,Jersey City,NJ,USA,North America  
 8,Aleena,Aman,Laptop,2,800,2015-05-25,Lahore,Punjab,Pakistan,Asia  
 10,Rita,Roger,Laptop,7,2100,2015-04-11,Paris,Île-de-France,France,Europe  
 1,Aamir,Shahzad,TV,1,700,2015-07-15,Charlotte,NC,USA,North America  
 12,aamir,Shahzad,TV,1,7000,2015-07-15,Charlotte,NC,USA,North America  
 10,Rita,Roger,Laptop,7,2100,2015-04-11,Paris,Île-de-France,France,Europe  
   

 // Let's get min and max value // study the error  
 let minid= (TotalSale | summarize min(id));  
 print minid  
   
   
   
 let minid= toscalar(TotalSale | summarize min(id));  
 let maxid= toscalar(TotalSale | summarize max(id)-5);  
 print minid,maxid  
   

 //use the value into next queries  
 let minid= toscalar(TotalSale | summarize min(id));  
 let maxid= toscalar(TotalSale | summarize max(id)-5);  
 TotalSale  
 | where id>minid and id<=maxid  
  

 
 // let's generate list of SalePersonFistName and use in query  
 TotalSale  
 | where SalePersonFName in ("Najaf","John")  
   
 let ListofNames=TotalSale | where id<5 | summarize makeset(SalePersonFName);  
  print ListofNames // it will not print because it is not scalar value  
   

   
  //Let's save a single scalar value then it should be able to print  
  let ListofNames=toscalar(TotalSale | where id<5 | summarize makeset(SalePersonFName));  
  print ListofNames // As it is scalar value, it should print fine.  
   

  // use this in clause  
  let ListofNames=toscalar(TotalSale | where id<5 | summarize makeset(SalePersonFName));  
  //print ListofNames  
  TotalSale  
  | where SalePersonFName in(ListofNames)  

Video Demo: How to Use To Scalar Function in Kusto Query Language