How to filter out a set of joined data using In Database Tools

[lasmithfla]

I know how to do this using the tools that aren't in database - there are too many records to datastream out to use desktop tools.

I have a set of data that at the beginning of a very large workflow, I would like to exclude the joined records.  I know if I was using desktop tools, I would just take the right and left side of the joined data and union it back together.  I'm struggling how to get this done using the in database tools

[Adrian_T]

Hey @lasmithfla,

You can achieve this by using a Filter In-DB tool after the Join In-DB Tool. You are essentially looking at an OUTER JOIN, which can be visualized in the below diagram and its corresponding SQL query.

What we want to do is to replicate this using In-DB tools in Alteryx. If we think about our expected results where we want to keep:

• Records from A that do not match B, AND
• Records from B that do not match A,

we essentially want a FULL OUTER JOIN of A and B, then filter out records where either A or B IS NULL. The below illustration explains this:

So here's how you can do this on Alteryx:

1. Use the Join In-DB Tool and change the Join Type in the configuration pane to a FULL OUTER JOIN.

2. Use a Filter In-DB Tool to filter for records where either [A.Key] OR [B.Key] ISNULL.

This is how it would look like:

Hope this helps! Let me know if it works for you 😊