How Do I Replicate the WHERE EXISTS Functionality of SQL w/in Alteryx?

Question

Hello, All:

I am trying to replicate the WHERE EXISTS functionality of SQL within Alteryx.  I have at least two situation for which I could use this.

The first situation is during a UNION.  I want to append dataflow #2 to dataflow #1 if the key value of #2 does not exist in #1.  After #1 and #2 are combined into a single data set, I may need to append a third data flow only if the key value in #3 does not exist in of aforementioned combined data set.  I do not want the key value to appear more than once in my final data set.

The second situation involves selecting/updating records from table #1 based on the contents of table #2.  For example, in the statement below, I want the names of customers who had orders during 2016.

select c1.customer_number
      ,c1.customer_name
from customers c1
where     1 = 1
      and exists  (select *
                   from customer_orders c2
                   where      1 = 1
                         and c1.customer_number = c2.customer_number
                         and c2.order_year = 2016
                  )
;

Thank you, much.

Jeff

RodL · Accepted Answer

Attached is example of one way to approach both of the situations you describe.

In the first situation you take the unmatched records from the source you are appending data from out of a join between the two data sets and union it back to the data set you are appending to.

In the second situation you create your "look up list" out of a filter (for whatever you set as the condition...in your example, a year that is in the data) and join the filtered data back with the other data source. (I've shown that you unselect the data that comes from the filtered source since my assumption is you are just using it for filtering purposes.)