if - else type of question in kusto

Bexy Morgan 255 Reputation points
2023-10-25T13:32:42.72+00:00

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"
Azure Data Explorer
Azure Data Explorer
An Azure data analytics service for real-time analysis on large volumes of data streaming from sources including applications, websites, and internet of things devices.
497 questions
{count} votes

1 answer

Sort by: Most helpful
  1. KranthiPakala-MSFT 46,432 Reputation points Microsoft Employee
    2023-10-27T02:27:28.0033333+00:00

    @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.

    1 person found this answer helpful.
    0 comments No comments