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

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?

Database Connection

Accepted answers

danilang

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

All comments

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

SQL_Joins.svg.png

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. join config selector.png

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!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703