In-DB tool: Filtering out a column from another table

[gelvira]

Hello,

I am using SQL and in-db tools for my analysis. I can't take my data outside DB as it is huge.  I want to filter out my MAIN file from another file which has an exclusion list. I know the SQL query but I don't understand how to do this in alteryx.

Ideally I need the following in middle of my workflow in Alteryx:

Select * from ABC 

where symbol not in (select symbol from XYZ)

I need to do this using only in-db tools. I know I can use the exact SQL query initially when I retrieve the data but is there any way to do it in middle of my workflow in-db? 

[TheOC]

Hi @gelvira 

I believe you should be able to import all of the data using connect in-DB. You can use multiple connect in-db tools to bring in your exclusion list too. 

You could then use a join in-db tool, with an outer join selected, to only take those results that dont join. This will give you a way of filtering the data with another table, within the in-db tools.

Hope this helps, and sorry i couldn't send you an example workflow to demonstrate.

Cheers,
TheOC

[gelvira]

Hello TheOC, 

Thanks for your response. 

I can import all data using multiple connect in-db. But the issue is with in-db join. 

I can't use SQL function like 'Minus' in my join to filter the exclusive data.  

I see that in-db join only allows inner. left outer, right outer and full join. I need left join where 2nd Table join is null. 

I can't edit the in-db join functionality. Is there any way around it? 

[TheOC]

hi @gelvira 

Sorry if I'm misunderstanding, but if you are joining on symbol, would a left outer join work? 

In that case, you will get all results of the left (in this case, your dataset) that don't join to the right (the exception table).

Cheers,
TheOC

[gelvira]

No problem. That's where I am confused.

 Please correct me if I am wrong...-From what I understand the join which is outside DB give exclusive A data, B data and combined data in Alteryx  but in-db join doesn't. . 

Take a look at the image- when I do LEFT JOIN, I get the top left figure. But I need the one below it, which is second left image. 

So to get the exclusion list I need LEFT JOIN without the common data from second table i.e. without B values. (Exclusively A) 

Regards,

Gelvira

[danilang · Accepted solution]

Hi @gelvira 

You can use a left outer join and follow this with a filter in-db with XYZ.symbol is null.

Another options is to rearrange your logic and start with  

Select * from ABC 

where symbol not in (select symbol from XYZ)

directly in the Connect In-DB tool.  

Dan

[apathetichell]

Hi @danilang and @TheOC are correct - look at the bottom of the Join In-Db and you can select the type of join you want. Select Left Outer Join from the config as pictured.

[gelvira]

Hello Danilang, 

I will try the first method you suggested. 

The second method is not ideal for my use case.  

Anyway, I found a way out. The In-DB tool lets me call another table probably since the odbc connection is the same. I am using the SQL code logic of "ABS not in (select * from XYZ) "  in the middle of my workflow, and it worked! 

Thanks!

[gelvira]

Hey Apathetichell, 

I do not need LEFT JOIN for my use case. I needed LEFT JOIN minus the common part in A & B. 

I found another way to do it.

Thanks!