Apr 18 2022 01:55 AM
I Have three columns and if the data in both the three columns matches a specific value , it should not give that result. I can't use "and" as it is not giving the expected result
A | B | C |
john | first | last |
deep | first | last |
john | first | last |
I want a query which will only display the second row. and operator would not give the expected result
Apr 18 2022 05:09 AM
Apr 18 2022 08:50 AM - edited Apr 18 2022 09:10 AM
This should work.
| where not(A == "john" and B == ''first" and C == "last")
(A == "john" and B == ''first" and C == "last") will get the specific combination of the three rows, so negating this will get anything that is not that specific combination.
Maybe you tried something like this? which would not show anything since each row will be checked separately. B and C are identical on the row that you want, and the rows you don't want, and thus nothing will show.
| where A != "john" and B != ''first" and C != "last"
Apr 19 2022 05:09 AM
You (may) need an "or" as well? However it's hard to tell unless you provide some sample data, and the KQL so far, like @Gary Bushey suggested - a DataTable is a good idea. e.g.
let table_ = datatable (fullName_:string, firstName_:string, lastName_:string)
[
'john smith' ,'john' ,'smith' ,
'joan smith' ,'joan' ,'smith' ,
'deep end' ,'deep' ,'end' ,
'john jones' ,'first' ,'last' ,
];
table_
| where fullName_ != 'john smith' and ( firstName_ != 'john' or lastName_ != 'smith' )
or, depending on what in Column A?
let srch = 'joan smith';
let table_ = datatable (fullName_:string, firstName_:string, lastName_:string)
[
'john smith' ,'john' ,'smith' ,
'joan smith' ,'joan' ,'smith' ,
'deep end' ,'deep' ,'end' ,
'john jones' ,'first' ,'last' ,
];
table_
| where fullName_ != srch and ( firstName_ != split(srch,' ')[0] or lastName_ != split(srch,' ')[1] )