Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

How to use IN clause in alteryx

tanvir_khan
8 - Asteroid

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! 

11 REPLIES 11
Tyro_abc
11 - Bolide

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

 

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

 

Regards

Arundhuti

grazitti_sapna
17 - Castor

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.

 

grazitti_sapna_0-1607319152548.png

 

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

 

Thanks.

Sapna Gupta
Qiu
21 - Polaris
21 - Polaris

@tanvir_khan 
Add to @Tyro_abc 

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

[Inspection ID] IN (1640216,1647217)
tanvir_khan
8 - Asteroid

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
21 - Polaris
21 - Polaris

@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.

Qiu_0-1607321070455.png

 

grazitti_sapna
17 - Castor

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.

 

grazitti_sapna_0-1607321065981.png

 

It is done in table or query section.

grazitti_sapna_0-1607321261537.png

 

 

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

 

Thanks.

 

 

Sapna Gupta
tanvir_khan
8 - Asteroid

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
5 - Atom

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.

image.png

DonnaFazio
6 - Meteoroid

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

Labels