Forum Discussion
Inject column as parameter in a defined function inside a join
I have a function daysOfMonthNoWeekends which returns a number with a date as parameter.
I have a join where I want to use this function in the summarize.
Problem: I can't use one of my date columns as the function parameter.
Working query:
let eventTable = Events_CL;
let customerTable = Customers_CL;
//MyFunction
let daysOfMonthNoWeekends=(event_date_t:datetime) {
toscalar(range days from startofmonth(event_date_t) to endofmonth(event_date_t) step 1d
| where dayofweek(days) between(1d .. 5d)
| summarize count()) };
//Join query
let usersByDayNoWeekends = eventTable
| join customerTable on ($left.license_id_d == $right.license_id_d and $left.event_date_t == $right.datetime_t)
| where license_type_s == "COMMERCIAL"
and license_status_s == "VALID"
and dayofweek(event_date_t) between(1d .. 5d)
| distinct name_s, event_date_t
// if I put daysOfMonthNoWeekends(event_date_t) in the next line is failing. Using now() as parameter works
| summarize uniqueUsersByMonth = count() / todouble(daysOfMonthNoWeekends(now())) by format_datetime(event_date_t, "yyyy-MM")
;
usersByDayNoWeekends
In the summarize line, in the line before you can see what I want to do but is not working.
Generated error:
Semantic error: '' has the following semantic error: Unresolved reference binding: 'event_date_t'.
Any idea? Thanks in advance!
4 Replies
I got lost with the JOIN, couldn't you simplify, like this, then join that to the other table?
Heartbeat | where Computer == "RETAILVM01" | where TimeGenerated > startofmonth(now()) | where dayofweek(TimeGenerated) between(1d .. 5d) | summarize count() by Computer, bin(TimeGenerated, 1d), format_datetime(TimeGenerated, "yyyy-MM-dd") | order by TimeGenerated ascI
I've simplified it in order that you see what I mean.
This works (using now() in the function call:
let daysOfMonthNoWeekends=(_event_date_t:datetime) { toscalar(range days from startofmonth(_event_date_t) to endofmonth(_event_date_t) step 1d | where dayofweek(days) between(1d .. 5d) | count) }; // MyTable_CL | extend daysOfMonthNoWeekends = daysOfMonthNoWeekends(now())And this doesn't works:
let daysOfMonthNoWeekends=(_event_date_t:datetime) { toscalar(range days from startofmonth(_event_date_t) to endofmonth(_event_date_t) step 1d | where dayofweek(days) between(1d .. 5d) | count) }; // MyTable_CL | extend daysOfMonthNoWeekends = daysOfMonthNoWeekends(TimeGenerated) //or with another column of MyTable like event_date_t fails too //| extend daysOfMonthNoWeekends = daysOfMonthNoWeekends(event_date_t)Error:
Semantic error: '' has the following semantic error: Unresolved reference binding: 'TimeGenerated'.
For the record I pretend to add a column with the number of days without weekends in a month based on a column which is a date.
