We have extended our Early Bird Tickets for Inspire 2023! Discounted pricing goes until February 24th. Save your spot!

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