DataSet Views - Outer Join

genaussie

Hi all,

Relatively new to Domo and really enjoying the experience so far :)

I've been taking full advantage of DataSet Views and love how easy this facility is to use. One suggestion though. The online information states that "we support Left, Right, and Inner Joins". Why not outer joins though so that the option to retain duplicate information is available? Whilst I appreciate this can be achieved via alternative methods, I feel it would also make sense for it to be one of the options in DataSet Views.

Cheers

jaeW_at_Onyx

@genaussie , while outer joins are nice, on most days of the week it is completely inappropriate to use an outer join.

i appreciate that it'd be nice for the feature to be supported because ... it's SQL, but in the over 200 projects i've implemented for clients LEFT or INNER JOINs have been sufficient.

In my experience, most people using an OUTER JOIN are using it as a crutch b/c they don't understand more performant ways of bringing their data together in Domo. More specifically, the use case for OUTER JOIN tends to be when you're joining two fact tables together. Don't do it. You will end up with unexected row growth and data duplication that then requires the use of 'crazy math' to calculate your metrics. Usually you end up with SUM(DISTINCT) or MAX() functions which work logically but have very hard boundaries on how you can / should use them.

Instead, get used to using UNION to append data into sparsely populated datasets but where you can take the clean SUM() of your metric columns

Here for example. https://www.youtube.com/watch?v=PVbOeLSae9o&t=820s

genaussie

thanks @jaeW_at_Onyx

I appreciate what you've said, and no doubt appending rows is typically a more useful way to join datasets.

Cheers