Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

How can I filter with an array?

hellyars
13 - Pulsar

I want to filter out multiple rows using their IDs.    I want to filter something along the lines [ID] !=('A1','A2',A3','B4',X5') without using the noob approach of having ten thousand AND statements.

 

Any quick ideas?

 

Thank you ?

8 REPLIES 8
Dynamomo
11 - Bolide

Hi @hellyars,

You use the Value IN operator

!([ID] IN ('A1','A2',A3','B4',X5')) or [ID] NOT IN ('A1','A2',A3','B4',X5') 

will work

Dynamomo
11 - Bolide

However, if it is a really long list of IDs, I would probably just bring that list in as an input and then use a join and select whatever did NOT join (as those IDs are not the ones you want).

hellyars
13 - Pulsar

Works perfectly for small sets.  Thank you.

dmccandless
8 - Asteroid

For big sets is this the most performant method?

 

Joining gets the job done, but I was wondering if there is a way (and if it's more performant) that you can have something like a select sub-query.

 

For example:

 

select * from employee where employee_ID in

(

select employee_ID from employee_compensation where annual_comp > 200000

);

 

or similarly:

 

select * from employee where employee_ID not in

(

select employee_ID from employee_compensation where annual_comp > 200000

);

dmccandless
8 - Asteroid

Trying to answer my own question - I guess one way to do this is using the find and replace tool.

Dynamomo
11 - Bolide

Yes a Find/Replace with a Filter after it would work.  Not sure if it would be faster.

Of course, if you had a really big dataset, you could use in-database tools as well and run the entire process in the database.

robinsadhwani1602
7 - Meteor

Could you provide the screenshot?

 

I am trying something like this.

 

Select column1, column2 from table 1 left join table 2 where id in (select id from table 1 inner join table 2 where id > 10)

Dynamomo
11 - Bolide

@robinsadhwani1602  I'm not a SQL pro but if what you are trying to do is join table 1 and 2 together where ID in table 1 is >10 (and just keep columns 1 and 2 from table 1) then I would use a filter on table 1 to select records where ID >10 then join the results of that to Table 2 and deselect the fields you don't want in the join tool.

 

Labels