How to use IN clause in alteryx

[tanvir_khan]

Hi

is it possible to implement SQL IN clause using alteryx tool(s)?

for example,

select * from table1

where ID IN

('10','20','30') 

if the number of values are less we can directly put those within the query of Input data node as above. But it's not feasible if there is couple of thousand records. Is there any alternate way to do this in alteryx? 

thanks! 

[Tyro_abc]

Yes, you can use "IN" without "WHERE", like below.

    [Inspection ID] IN ("1640216","1647217")

Regards

Arundhuti

[grazitti_sapna]

Hi @tanvir_khan, you can try using a filter tool after reading the data from the file. Please refer to the attachment and screenshot below.

If this is what you are looking for kindly mark this post as solution.

Thanks.

[Qiu]

@tanvir_khan 
Add to @Tyro_abc 

In case your [Inspection ID] field is numeric, then 

[Inspection ID] IN (1640216,1647217)

[tanvir_khan]

Hi .@grazitti_sapna and .@Qiu 

I think my problem title didn't quite reflect what I'm looking for. Sorry guys.

Basically I want to filter out while I'm fetching from database cause the number of records are millions and it's time consuming. So, instead of fetching all the records, I want to filter with values [this is again couple of thousands].

For example,

1.

DB-Table

50 million records

2.

I need to look up 50k records only out of 50M.

So, I don't want to get all the 50M records, I'd like to fetch 50k out of 50M and analyse those only.

We do such thing in database by creating a look up table and using IN clause in the query. How can we do it in alteryx?

Thanks! 

[Qiu]

@tanvir_khan 
When you are using Alteryx to pull out the data, you will be using the almost same SQL query right?

so you can basically do what you do in database side, I suppose.

[grazitti_sapna]

Hi @tanvir_khan , you can write the same query  as mentioned in the screenshot. When you create a connection with the database it will automatically open up this page where you can select the sql editor and write your own custom query.

It is done in table or query section.

Apart from that I do not think there is any other particular way rather than filtering whole set of data.

Thanks.

[tanvir_khan]

Hi .@Qiu & .@grazitti_sapna 

Yes, if the look up values are less i.e. up to couple to hundreds it can be done like this. But the query will not support if there are couple of thousands values.

It can be done by creating a table in DB, insert the values and use the table inside IN clause of the query. But my limitation here is - I don't have access in that database. So, I'm stuck. 😞

[amueller2]

I know this thread is not active, but I stumbled across it and I've been in a similar situation before. What I did was use get the list of values that I needed, and then write a SQL query in a dynamic input tool like 

SELECT * WHERE column = [value]

I then used the Dynamic input tool with the "Modify SQL Query" option with "Replace a Specific String" to replace string "[value]" with the necessary value.

[DonnaFazio]

Is it possible to use an IN statement where it does not contain?