Joining two tables

[samgar-kali]

I am trying to join two tables. dt.entity.service and "Davis problem events". I believe the syntax is correct, and I am not seeing any errors. However, the following DQL shows that there are no matching services. Which sounds incorrect. Am I missing something?

fetch events, from:now()-24h, to:now()
| filter event.kind == "DAVIS_PROBLEM"
| fields event.start, event.end, display_id, event.category, event.name, affected_entity_ids, root_cause_entity_id, event.status, resolved_problem_duration
| sort event.start desc
| lookup [fetch dt.entity.service],sourceField:affected_entity_ids,lookupField:id
| summarize count=count(), by:{lookup.entity.name}

[Tiit_Hallas · Accepted solution]

The issue is coming from different data types: the source field "affected_entity_ids" is an array, while the lookup field "id" is a string. The quickest fix here would be to use "expand" to break down the array, e.g: 

fetch events, from:now()-24h, to:now()
| filter event.kind == "DAVIS_PROBLEM"
| fields event.start, event.end, display_id, event.category, event.name, affected_entity_ids, root_cause_entity_id, event.status, resolved_problem_duration
| expand affected_entity_ids
| lookup [fetch dt.entity.service], sourceField:affected_entity_ids, lookupField: id