Different array types, for comparison in the set_difference() function

user_3728562 0
Viewed 174

I'm having some issues getting expected results from set_difference(). I assumed I was comparing two dynamic arrays, but I'm not sure where the gap is. The only additional insight I have is that when I compare the two arrays using the gettype() function, I get the following:

First array

  • Created using a make_list aggregation, e.g.
| summarize inv_list = make_list(Date)
  • When I run gettype() on the array:
"type_inv_list": array

Second array

  • Created through a scalar function
let period_check_range = todynamic(range(make_datetime(start_date), datetime_add('day',8,make_datetime(start_date)),1d));
  • When I run gettype() on the array:
"type_range___scalar_90e56a216d8942f28e6797e5abc35dd9": array

Any guidance on how to make these arrays work so I can use the set_difference() function?

kql azure-data-explorer
1 Answers

You're missing toscalar() (see doc) in the first array. When you run | summarize ... you get a table as a result, but what you actually want is a single scalar, what's why toscalar() is needed.

Here's how to achieve what you want:

let StartDate = ago(10d);
let Array1 = toscalar(MyTable | summarize make_set(Timestamp));
let Array2 = todynamic(range(make_datetime(StartDate), datetime_add('day',8,make_datetime(StartDate)),1d));
print set_difference(Array2, Array1)

By the way, you probably want to use make_set and not make_list as you're not interested in duplicate values.

user_21450970
Related