May 08 2020
03:24 AM
- last edited on
Apr 08 2022
10:26 AM
by
TechCommunityAP
May 08 2020
03:24 AM
- last edited on
Apr 08 2022
10:26 AM
by
TechCommunityAP
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!
May 08 2020 04:57 AM
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 asc
I
May 08 2020 06:02 AM
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.
Sep 10 2021 12:48 PM
Sep 10 2021 12:57 PM