How to call a function with in function based on the passed parameter values

Question

How to call a function with in function based on the passed parameter values

Bexy Morgan 260

.create table inputtable (Country:string, Export:string, Import:string, Rank:int)

.ingest inline into table ["inputtable"] <|
"US","Refined petroleum","Clothing",1
"US","Aircraft parts","Toys",2
"US","Cars","Computers",3
"US","Cars","Computers",4
"Spain","Vehicles","Food",1
"Spain","Vehicles","Food",2
"Spain","Vehicles","Food",3
"Spain","Vehicles","Beverages",4
"Spain","Vehicles","Food",5

.create function
with (docstring = 'Your description', folder='Your folder')
 fun1_select_records(_export: string, _import: string)  
 {
    inputtable 
    | where Export == _export and Import == Import
}

//fun1_select_records(_export = "Cars", _import = "Computers")

.create-or-alter  function
with (docstring = 'Your description', folder='Your folder')
 fun2_select_records(_export: string)  
 {
    inputtable 
    | where Export == _export
    | project Country
}

//fun2_select_records(_export = "Cars")

.create function resultant(Export:dynamic, function_number:int)
{
// Please help how to write this kql Query based on parameter vale for function_number
// if the parameter function_number is given as 1 then fun1_select_records should be executed, 
// if the parameter function_number is given as 2 then fun2_select_records should be executed
}

How to write the resultant( ) function so that when function_number =1 it should run the fun1_select_records;if function_number=2 resultant ( ) should run fun2_select_records

QuantumCache 20,366 Moderator

Hello @Bexy Morgan

you can try the case statement in your resultant function to conditionally execute the desired function based on the value of the function_number parameter. Something like the below snippet.

.create table inputtable (Country:string, Export:string, Import:string, Rank:int)

.ingest inline into table ["inputtable"] <|
"US","Refined petroleum","Clothing",1
"US","Aircraft parts","Toys",2
"US","Cars","Computers",3
"US","Cars","Computers",4
"Spain","Vehicles","Food",1
"Spain","Vehicles","Food",2
"Spain","Vehicles","Food",3
"Spain","Vehicles","Beverages",4
"Spain","Vehicles","Food",5

.create function
with (docstring = 'Select records based on Export and Import', folder='Your folder')
fun1_select_records(_export: string, _import: string)  
{
    inputtable 
    | where Export == _export and Import == _import
}

.create-or-alter function
with (docstring = 'Select records based on Export', folder='Your folder')
fun2_select_records(_export: string)  
{
    inputtable 
    | where Export == _export
    | project Country
}

.create function resultant(Export:dynamic, function_number:int)
{
    case function_number
    (
        1, fun1_select_records(Export, _import),
        2, fun2_select_records(Export),
        "Invalid function number"
    )
}
resultant(Export = "Cars", function_number = 1)

I hope this helps! Let me know if you have any further questions.

2 answers

Your answer

QuantumCache 20,366 Reputation points Moderator

2023-11-01T23:02:46.0466667+00:00

Hello @Bexy Morgan

you can try the case statement in your resultant function to conditionally execute the desired function based on the value of the function_number parameter. Something like the below snippet.

.create table inputtable (Country:string, Export:string, Import:string, Rank:int) .ingest inline into table ["inputtable"] <| "US","Refined petroleum","Clothing",1 "US","Aircraft parts","Toys",2 "US","Cars","Computers",3 "US","Cars","Computers",4 "Spain","Vehicles","Food",1 "Spain","Vehicles","Food",2 "Spain","Vehicles","Food",3 "Spain","Vehicles","Beverages",4 "Spain","Vehicles","Food",5 .create function with (docstring = 'Select records based on Export and Import', folder='Your folder') fun1_select_records(_export: string, _import: string) { inputtable | where Export == _export and Import == _import } .create-or-alter function with (docstring = 'Select records based on Export', folder='Your folder') fun2_select_records(_export: string) { inputtable | where Export == _export | project Country } .create function resultant(Export:dynamic, function_number:int) { case function_number ( 1, fun1_select_records(Export, _import), 2, fun2_select_records(Export), "Invalid function number" ) } resultant(Export = "Cars", function_number = 1)

I hope this helps! Let me know if you have any further questions.

Answer 1

Bexy Morgan 260

Hi Satish, the query is giving syntax error. Can you please check again, Thanks.

User's image

Bexy Morgan 260 Reputation points

2023-11-02T21:09:22.4533333+00:00

Hi @Wilko van de Velde , sorry to tag you here. Can you please help how to write this resultant( ) function. The given resultant ( ) function query is having error and not giving desired output.

Answer 2

Wilko van de Velde 2,236

Hi @Bexy Morgan ,

I didn't found a conditional option (if, case, switch) to use in a Kusto function. The best thing I found was using a union. Downfall is that it will show all columns of the the tables in the union.

So, I needed to solve this by the narrow operator. Then, removed all columns without any values (I also excluded "##(null)" values which the narrow operator creates of ints) .

.create-or-alter function resultant(Export:dynamic, function_number:int)
{        
     //create an empty datatable so the union can be used
    datatable() []
    | union (fun1_select_records(Export, "Computers") | where function_number == 1), 
            (fun2_select_records(Export) | where function_number == 2) 
	//Use narrow function to exclude empty columns
    | evaluate narrow()
    | where isnotempty(Value) and Value != "##(null)"
	//pivot the table again to the original format
    | evaluate pivot(Column, any(Value), Row)
    | project-away Row
}

resultant(Export = "Cars", function_number = 2)

More info:

https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/unionoperator?pivots=azuredataexplorer

https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/narrowplugin

Hope this will help.

Kind regards,

Wilko

Please do not forget to "Accept the answer” wherever the information provided helps you, this can be beneficial to other community members. If you have extra questions about this answer, please click "Comment".

Bexy Morgan 260 Reputation points

2023-11-03T09:42:15.2233333+00:00

Thanks @Wilko van de Velde , how to we parameterize this "Computers" , instead of directly passing into fun1_select_records parameters

Also, Is that possible if the sent parameters has no records to return, can we print some thing like "No records for passed parameters"?
Wilko van de Velde 2,236 Reputation points

2023-11-03T09:56:06.9266667+00:00
By adding a optional parameter to the function, it is possible to parametrize "Computers". Be aware that fun1_select_records could receive a null value.

.create-or-alter function resultant(Export:dynamic, Import:dynamic = dynamic(null), function_number:int) { //create an empty datatable so the union can be used datatable() [] | union (fun1_select_records(Export, Import) | where function_number == 1), (fun2_select_records(Export) | where function_number == 2) | evaluate narrow() | where isnotempty(Value) and Value != "##(null)" | evaluate pivot(Column, any(Value), Row) | project-away Row }

What do you mean exactly by, the sent parameters has no records to return? Should it return the message when using an invalid function_number (for example 3)? Or should the message be returned when, for example, using function_number=1 and there are no records?
Bexy Morgan 260 Reputation points

2023-11-03T10:04:13.1466667+00:00

The message should be returned when, for example, using function_number=1 and there are no records if there is no records for the passed parameters.

Wilko van de Velde 2,236

I changed the function:

.create-or-alter function resultant(Export:dynamic, Import:dynamic = dynamic(null), function_number:int)
{        
    //get data from functions
    let fun1 = fun1_select_records(Export, Import) | where function_number == 1;
    let fun2 = fun2_select_records(Export) | where function_number == 2;
    //Count records of both functions
    let fun1_has_records = toscalar(fun1 | summarize count() > 0);
    let fun2_has_records = toscalar(fun2 | summarize count() > 0);
    //create an empty datatable so the union can be used
    datatable(Message: string) ["No records for passed parameters"]
    //Only show the message when both functions have no records
    | where fun1_has_records == false and fun2_has_records == false
    | union (fun1), 
            (fun2) 
    | evaluate narrow()
    | where isnotempty(Value) and Value != "##(null)"
    | evaluate pivot(Column, any(Value), Row)
    | project-away Row 
}

When executing a valid request:

resultant(Export = "Cars", function_number = 2)

Then you will get the result of the function:

User's image

But when executing a invalid request:

resultant(Export = "invalid", function_number = 2)

Then, you will receive the (error) message:

User's image

Bexy Morgan 260 Reputation points

2023-11-03T13:55:17.07+00:00

Awesome. Thanks @Wilko van de Velde
QuantumCache 20,366 Reputation points Moderator

2023-11-15T04:47:48.6366667+00:00

Bexy Morgan Thanks for your patience, and my apologies for the delayed response in this matter, i was out of office. Looks like the issue is now resolved!

Please "Accept Answer" from Wilko, so that we can close this case.

Share via

How to call a function with in function based on the passed parameter values

2 answers

Your answer