Extend the column in primary table

Question

I want to extend table 1 as IsAvailableInTable2 = Yes, if there's a value matching Id (Column in Table1) in column MonitorId in Table 2. I tried using materialize but didn't quite work out.

//Table 1
datatable(Id:string, MetadataKey:string, MetadataValue:string, Key:string, Value:string)
[
    "Restarting", "", "", "", "", 
    "Starting", "", "", "", "", 
    "Failed", "Location", "Monitor", "Routing", "xyz", 
]

//Table 2
datatable(MonitorId:string, Automitigated:long, AlertCount:long)
[
    "Restarting", 94, 94, 
    "Restart", 93, 93, 
    "Failed", 92, 92, 
]

Expected Result:

enter image description here

could you please clarify why `IsAvailableTable2` appears as `No` in your expected result, if `Failed` appears in both `Table1\Id` and `Table2\MonitorId`? — Yoni L., Feb 01 '22 at 20:49

score 2 · Answer 1 · answered Feb 01 '22 at 20:52

This should work:

// creating the first table. optional, assuming the data is ingested, and not a 'datatable' literal
.set T1 <| datatable(Id:string, MetadataKey:string, MetadataValue:string, Key:string, Value:string)
[
    "Restarting", "", "", "", "", 
    "Starting", "", "", "", "", 
    "Failed", "Location", "Monitor", "Routing", "xyz", 
]


// creating the second table. optional, assuming the data is ingested, and not a 'datatable' literal
.set T2 <| datatable(MonitorId:string, Automitigated:long, AlertCount:long)
[
    "Restarting", 94, 94, 
    "Restart", 93, 93, 
    "Failed", 92, 92, 
]

// query
T1
| extend IsAvailableInTable2 = case(Id in ((T2 | project MonitorId)), "Yes", "No")

Id	MetadataKey	MetadataValue	Key	Value	IsAvailableInTable2
Restarting					Yes
Starting					No
Failed	Location	Monitor	Routing	xyz	Yes

Thank you , Yoni! If I have to project few columns from the Table2 in Table1, Will I need to do a join? Also - Failed should be 'Yes'. It was a typo. Thanks! — SM04, Feb 02 '22 at 02:34

Extend the column in primary table

1 Answers1