Inject column as parameter in a defined function inside a join

%3CLINGO-SUB%20id%3D%22lingo-sub-1373126%22%20slang%3D%22en-US%22%3EInject%20column%20as%20parameter%20in%20a%20defined%20function%20inside%20a%20join%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1373126%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20function%26nbsp%3BdaysOfMonthNoWeekends%20which%20returns%20a%20number%20with%20a%20date%20as%20parameter.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20join%20where%20I%20want%20to%20use%20this%20function%20in%20the%20summarize.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EProblem%3A%20I%20can't%20use%20one%20of%20my%20date%20columns%20as%20the%20function%20parameter.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWorking%20query%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3Elet%20eventTable%20%3D%20Events_CL%3B%0Alet%20customerTable%20%3D%20Customers_CL%3B%0A%2F%2FMyFunction%0Alet%20daysOfMonthNoWeekends%3D(event_date_t%3Adatetime)%20%7B%20%0Atoscalar(range%20days%20from%20startofmonth(event_date_t)%20to%20endofmonth(event_date_t)%20step%201d%0A%7C%20where%20dayofweek(days)%20between(1d%20..%205d)%0A%7C%20summarize%20count())%20%7D%3B%0A%2F%2FJoin%20query%0Alet%20usersByDayNoWeekends%20%3D%20eventTable%0A%7C%20join%20customerTable%20on%20(%24left.license_id_d%20%3D%3D%20%24right.license_id_d%20and%20%24left.event_date_t%20%3D%3D%20%24right.datetime_t)%0A%7C%20where%20license_type_s%20%3D%3D%20%22COMMERCIAL%22%0A%20and%20license_status_s%20%3D%3D%20%22VALID%22%0A%20and%20dayofweek(event_date_t)%20between(1d%20..%205d)%0A%7C%20distinct%20name_s%2C%20event_date_t%0A%2F%2F%20if%20I%20put%20daysOfMonthNoWeekends(event_date_t)%20in%20the%20next%20line%20is%20failing.%20Using%20now()%20%20as%20parameter%20works%0A%7C%20summarize%20uniqueUsersByMonth%20%3D%20count()%20%2F%20todouble(daysOfMonthNoWeekends(now()))%20by%20format_datetime(event_date_t%2C%20%22yyyy-MM%22)%0A%3B%0AusersByDayNoWeekends%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20summarize%20line%2C%20in%20the%20line%20before%20you%20can%20see%20what%20I%20want%20to%20do%20but%20is%20not%20working.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGenerated%20error%3A%3C%2FP%3E%3CP%3E%3CSPAN%3ESemantic%20error%3A%20''%20has%20the%20following%20semantic%20error%3A%20Unresolved%20reference%20binding%3A%20'event_date_t'.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3BAny%20idea%3F%20Thanks%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1373126%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1373264%22%20slang%3D%22en-US%22%3ERe%3A%20Inject%20column%20as%20parameter%20in%20a%20defined%20function%20inside%20a%20join%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1373264%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F660677%22%20target%3D%22_blank%22%3E%40dcalap%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20got%20lost%20with%20the%20JOIN%2C%20couldn't%20you%20simplify%2C%20like%20this%2C%20then%20join%20that%20to%20the%20other%20table%3F%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3EHeartbeat%0A%7C%20where%20Computer%20%3D%3D%20%22RETAILVM01%22%0A%7C%20where%20TimeGenerated%20%26gt%3B%20startofmonth(now())%0A%7C%20where%20dayofweek(TimeGenerated)%20between(1d%20..%205d)%0A%7C%20summarize%20count()%20by%20Computer%2C%20bin(TimeGenerated%2C%201d)%2C%20format_datetime(TimeGenerated%2C%20%22yyyy-MM-dd%22)%0A%7C%20order%20by%20TimeGenerated%20asc%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EI%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1374014%22%20slang%3D%22en-US%22%3ERe%3A%20Inject%20column%20as%20parameter%20in%20a%20defined%20function%20inside%20a%20join%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1374014%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20simplified%20it%20in%20order%20that%20you%20see%20what%20I%20mean.%3C%2FP%3E%3CP%3EThis%20works%20(using%20now()%20in%20the%20function%20call%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3Elet%20daysOfMonthNoWeekends%3D(_event_date_t%3Adatetime)%20%7B%0Atoscalar(range%20days%20from%20startofmonth(_event_date_t)%20to%20endofmonth(_event_date_t)%20step%201d%0A%7C%20where%20dayofweek(days)%20between(1d%20..%205d)%0A%7C%20count)%0A%7D%3B%0A%2F%2F%0AMyTable_CL%0A%7C%20extend%20daysOfMonthNoWeekends%20%3D%20daysOfMonthNoWeekends(now())%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EAnd%20this%20doesn't%20works%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3Elet%20daysOfMonthNoWeekends%3D(_event_date_t%3Adatetime)%20%7B%0Atoscalar(range%20days%20from%20startofmonth(_event_date_t)%20to%20endofmonth(_event_date_t)%20step%201d%0A%7C%20where%20dayofweek(days)%20between(1d%20..%205d)%0A%7C%20count)%0A%7D%3B%0A%2F%2F%0AMyTable_CL%0A%7C%20extend%20daysOfMonthNoWeekends%20%3D%20daysOfMonthNoWeekends(TimeGenerated)%0A%2F%2For%20with%20another%20column%20of%20MyTable%20like%20event_date_t%20fails%20too%0A%2F%2F%7C%20extend%20daysOfMonthNoWeekends%20%3D%20daysOfMonthNoWeekends(event_date_t)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EError%3A%3C%2FP%3E%3CP%3E%3CSPAN%3ESemantic%20error%3A%20''%20has%20the%20following%20semantic%20error%3A%20Unresolved%20reference%20binding%3A%20'TimeGenerated'.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EFor%20the%20record%26nbsp%3BI%20pretend%20to%20add%20a%20column%20with%20the%20number%20of%20days%20without%20weekends%20in%20a%20month%20based%20on%20a%20column%20which%20is%20a%20date.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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!

2 Replies

@dcalap 

 

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

@Clive Watson 

 

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.