Subquery in alteryx Filter tool

Question

Hi Guys,

I am trying to filter out data from my input tool on the basis of some condition.

If i have to explain it in SQL terminology then i want to use where clause with sub query.

Providing herewith screenshot for your reference.

There is on input data regarding rating where i want to take all the rating which are below B.

In SQL : select Rating from table where rank > (select rank from table where rating='B')

I search this type of question in community but was not able to find any ans to this.

Can i achieve this using Single Filter option?

Sincere apologies if question seems stupid but i am new to alteryx and stuck in this section for pretty long time.

Thanks in advance.

danilang · Accepted Answer

Hi @VishalKhot

If you break down what the SQL engine is doing in the background, you'd see a process similar to this

1. Find the record " where [rating]='B' " portion of the sub query,

2. Select the [Rank] column

3. Compare this value to each of the rows in the outer query and return the ones where [Rank]> the result of step 2

Now, Alteryx is designed to work with flat tables very quickly.  The filter tools in Alteryx only have access to one row of data at a time.  There is no syntax that you can use to perform summary operations directly within a filter.  To perform the equivalent of sub query, you need to break it down similar the process that the SQL query engine uses internally

The bottom branch performs the " where rating='B' " portion of the sub query,   The results of this are passed to the Append Fields tool, which performs the "select rank from table" part of the subquery, by only selecting the Rank field, renamed to RankCutOff.  This value is appended to all the rows in your input table.  The final Filter now has enough information available on each row to apply the outer query and return the rows you're looking for

Dan