I got the following dataset:
let data = datatable(Timestamp:datetime, Name:string, Value:int)
[
datetime(2022-02-18 10:00:00 AM), "AX_100A_A00", 100,
datetime(2022-02-18 10:01:00 AM), "BX_101B_B00", 200,
datetime(2022-02-18 10:02:00 AM), "CX_102C_C00", 300,
datetime(2022-02-18 10:03:00 AM), "DX_103D_D00", 400,
datetime(2022-02-18 10:04:00 AM), "EX_104E_E00", 500,
];
let mydict = dynamic(
{
"100A":"New York"
,"101B":"Geneva"
,"102C":"France"
,"103D":"US"
,"104E":"Canada"
}
);
data
| summarize result = max(Value) by Floor_Name = tostring(mydict[substring(Name, 3, 4)])
To illustrate what I am trying achieve here. Between the two underscores there is a code which represents a specific Location.
My question here, is how is it possible to add a condition which checks if the word between the 2 underscores doesn't exist as key in the dictionary, then just display the value between the 2 underscores. However if it does exist in the dictionary, then display its friendly name. Let us assume that a new name was added FX_105F_F00. In this case, since it is not found in the dictionary, then no friendly name should be displayed. It will be displayed as it is. An iff condition should be added to the floor name in the code but how should the syntax be ?
Few things to note here:
So, for 105F
, mydict["105F"]
is null and tostring(mydict["105F"]
) is an empty string, so coalesce proceed to the 2nd value (Floor_Code).
let data = datatable(Timestamp:datetime, Name:string, Value:int)
[
datetime(2022-02-18 10:00:00 AM), "AX_100A_A00", 100,
datetime(2022-02-18 10:01:00 AM), "BX_101B_B00", 200,
datetime(2022-02-18 10:02:00 AM), "CX_102C_C00", 300,
datetime(2022-02-18 10:03:00 AM), "DX_103D_D00", 400,
datetime(2022-02-18 10:04:00 AM), "EX_104E_E00", 500,
datetime(2022-02-18 10:05:00 AM), "FX_105F_F00", 600
];
let mydict = dynamic(
{
"100A":"New York"
,"101B":"Geneva"
,"102C":"France"
,"103D":"US"
,"104E":"Canada"
}
);
data
| extend Floor_Code = substring(Name, 3, 4)
| summarize result = max(Value) by Floor_Name = coalesce(tostring(mydict[Floor_Code]), Floor_Code)
Floor_Name | result |
---|---|
New York | 100 |
Geneva | 200 |
France | 300 |
US | 400 |
Canada | 500 |
105F | 600 |