CODEWITHSUNDEEP
azure-data-explorerkql
Sanjotha Sahair
Sanjotha Sahair

Reputation: 31

How to access a value in a kusto table at a specific row number and at a specific column number?

By using row_number() we can get the entire row. But if we want a specific value in that row and use that for further calculation how to do it?

Example:
Customer Month  Usage. %Change
ABC       8.     10.      0
DEF       9.     12.      120
GHI       10.    10.      100
JKL.      11.    15.      150

The calculation I am looking for is:- 1st month's usage is needed which is 10 From next month it will be

12/10 * 100 = 120
10/10 * 100 = 100
15/10 * 100 = 150

How to get the first row, second column value and store it into a variable so that I can use it for further calculation ?

Upvotes: 0

Views: 2402

Answers (1)

joelby
joelby

Reputation: 87

How about something like this?

let events = datatable(Customer: string, Month:int, Usage:real) [
"ABC",       8,     10,
"DEF",      9,     12,
"GHI",      10,    10,
"JKL",      11,    15];
//
let first_usage = toscalar(events | summarize arg_min(Month, Usage) | project Usage); 
events | extend Change = 100*Usage/first_usage

The first_usage variable is set to the Usage value for the lowest month. You could just pick the first row if you're sure they're already sorted though. We have to convert it to a scalar to use it in future calculations.

Next, we add a new Change column with your calculation. The first row will be 100 (rather than 0) but this could also be fixed if it's important, perhaps by using iff() to handle the first row differently:

let events = datatable(Customer: string, Month:int, Usage:real) [
"ABC",       8,     10,
"DEF",      9,     12,
"GHI",      10,    10,
"JKL",      11,    15];
//
let first_usage = toscalar(events | summarize arg_min(Month, Usage) | project Usage); 
events
| serialize
| extend r=row_number()
| extend Change = iff(r==1, 0.0, 100*Usage/first_usage)
| project-away r

Upvotes: 1

Related Questions