How to map the 1 column values into another new column using Kusto query

Bexy Morgan 255 Reputation points
2023-10-16T12:24:26.0633333+00:00

Objective: In sample_table, column A for the values of "Q","W" and "Cat" there should be a new column called "C" with mapping value as "CAT" in the output_table.

sample_table

.create table ['sample_table']  (['A']:string, ['B']:string)
//Insert new data into the source
.ingest inline into table ["sample_table"] <|
"Q","88"
"W","11"
"A","3"
"Ball","4"
"Cat","33"


Output Table:

.create table ['output_table']  (['A']:string, ['B']:string, ['C']:string)

.ingest inline into table ['output_table'] <|
"Q","88","MAP"
"W","11","MAP"
"A","3",
"Ball","4",
"Cat","33","MAP"

The output_table should be the desired result. Thanks.

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.
487 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Wilko van de Velde 2,226 Reputation points
    2023-10-16T13:10:05.19+00:00

    I'm not sure about the logic, but maybe something like this:

    sample_table
    | extend c = iif(A in ("Q","W","Cat"),"MAP",dynamic(null))
    

    Be aware that Kusto is case sensitive.


    Please do not forget to "Accept the answer” wherever the information provided helps you, this can be beneficial to other community members. If you have extra questions about this answer, please click "Comment".