if - else type of question in kusto

Question

when there is a match in tableA and tableB between columns Sno and Status then there should be a new column called "Identity" which has "Yes" if matching "No" if mismatch as show in outputtable

///////tableA///////
.create table tableA (Sno:int,Any:string,Status: string)

.ingest inline into table ["tableA"] <|
1,"US","a"
2,"US","x"
3,"USSR","y"
4,"UK","d"


////tableB/////
.create table tableB (Sno:int,Status: string)

.ingest inline into table ["tableB"] <|
1,"a"
2,"b"
3,"c"
4,"d"



/////The below outputtable show how the tableB should like/////////

.create table outputtable (Sno:int,Status: string, Identity:string)

.ingest inline into table ["outputtable"] <|
1,"a","yes"
2,"b","no"
3,"c","no"
4,"d","yes"

Answer 1

@Bexy Morgan Thanks for using Microsoft Q&A forum and posting your query.

Please try below and that should work:

let TableA = datatable(Sno: int, Any: string, Status: string)
[
    1, "US", "a",
    2, "US", "x",
    3, "USSR", "y",
    4, "UK", "d",
];
let TableB = datatable(Sno: int, Status: string)
[
    1, "a",
    2, "b",
    3, "c",
    4, "d",
];
TableA
| join kind = inner TableB on Sno
| project Sno, Status, Identity = case(Status == Status1, "Yes", "No")

Or

let TableA = datatable(Sno: int, Any: string, Status: string)
[
    1, "US", "a",
    2, "US", "x",
    3, "USSR", "y",
    4, "UK", "d",
];
let TableB = datatable(Sno: int, Status: string)
[
    1, "a",
    2, "b",
    3, "c",
    4, "d",
];
TableA
| project-rename LeftStatus = Status
| join kind = inner (TableB | project-rename RightStatus = Status) on Sno
| project Sno, Status = LeftStatus, Identity = case(LeftStatus == RightStatus, "Yes", "No")

Hope this helps.

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.