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 |
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" ] |