Event Attribute Join

[mod-credible]

Hello, I'm looking to view a join on two System Activity tables, Event Attribute and User, based on the Attribute Value column in Event Attribute. I see the existing explore for Event Attribute can join User using this join clause

```

FROM event_attribute
LEFT JOIN event ON event_attribute.event_id = event.id
LEFT JOIN user AS user ON event.user_id = user.id

```

What I would like is to add a join to see the user affected by the events, when appropriate:

```

LEFT JOIN user AS user_event_attribute ON event_attribute.value = user.id

```

I believe this is not possible in the System Activity explore or in SQLRunner, is there somewhere in looker this can be done?

[Kevin_F_McCarth · Accepted solution]

Hello mod-credible,

You are right: there's not currently a way to change/add custom joins into a system__activity explores.

One option that comes to mind is the 'Merged Results' feature, with which you effectively join two query's results.  

Caveats:
1) You would need to retain relevant user_id in the result, in order to use it as the merge rule.  You would not then be able to directly report on aggregations of this data (without, for example, exporting to excel and layer a pivot table onto the results).
2) As mentioned in the docs for merged results:
Merged Results is a post-query processing feature that, if not used thoughtfully, can overtax Looker instance resources and cause your Looker instance to respond more slowly for all users. 

I hope this helps a bit, good luck!

[mod-credible]

Thanks so much for the assistance! I was able to create a dashboard with a Merged Results tile on it. For anyone searching this in the future: I did have to convert the user id to strings using `concat(${user.id}, "")` in order to join on the event attribute value, which is a string itself, but all around success. I'm accepting your solution 🙇