Home > OS >  KQL conditional sub query
KQL conditional sub query

Time:04-12

Hello my friends on the internet,
I want to show rows in condition that they not appear in other ways on other rows.
For example I have this table:

Name LastName Status
Abby Abbson OK
Abby Abbson Not OK
Bobby Bobson Not OK
Sandra Mayer OK

I want to show all the people in Status 'Not OK' as long as they don't have also a record with 'OK' status, so for my example the result will be only:

Name LastName Status
Bobby Bobson Not OK

I'm lost already if someone can please give me a guideline for this it will be great, thanks!

CodePudding user response:

In this scenario, you just need to verify that there are no OK records for a person.

datatable(Name:string,LastName:string,Status:string)
[
     "Abby"  ,"Abbson" ,"OK"
    ,"Abby"  ,"Abbson" ,"Not OK"
    ,"Bobby" ,"Bobson" ,"Not OK"
    ,"Sandra","Mayer"  ,"OK"
]
| summarize countif(Status == "OK") by Name, LastName
| where countif_ == 0
Name LastName countif_
Bobby Bobson 0

Fiddle

CodePudding user response:

Does this work?

datatable (Name: string, LastName: string, Status: string) [
    "Abby","Abbson","OK",
    "Abby","Abbson","Not OK",
    "Bobby","Bobson","Not OK",
    "Sandra","Mayer","OK"
]
| summarize make_set(Status) by Name, LastName
| where array_index_of( set_Status,"Not OK") >= 0 and array_length( set_Status) == 1
Name LastName set_Status
Bobby Bobson [
"Not OK"
]
  • Related