Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

General Discussions

Discuss any topics that are not product-specific here.

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

lasmithfla
6 - Meteoroid

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

1 REPLY 1
Adrian_T
Alteryx
Alteryx

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.

Adrian_T_0-1665217947274.png

 

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:

 

Adrian_T_1-1665218677194.png

 

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.

Adrian_T_2-1665219007435.png

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:

Adrian_T_3-1665219254054.png

 

 

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

 

Labels