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

Filter Help

Brad1
11 - Bolide

I have this in my Filter tool but it doesn't work:

 

[Network Id] in ("a", "b", "c", "d") AND [Include/Exclude]="Include" AND [Service Type] not in ("1", "2")

 

I only get back Network ID "a" for "Include and any Service Type not "1" or "2".

 

In Access this works:

 

WHERE ((([mytbl].[Network Id])="a" Or ([mytbl].[Network Id])="b" Or ([mytbl].[Network Id])="c" Or ([mytbl].[Network Id])="d") AND (([mytbl].[Include/Exclude])="Include") AND (([mytbl].[Service Type])<>"1" And ([mytbl].[Service Type])<>"2"));

 

With Access, I get all Networks listed minus "Exclude"s and minus Serv Type "1" and "2".  Any idea?  Thanks.

9 REPLIES 9
MarqueeCrew
20 - Arcturus
20 - Arcturus

@Brad1,

 

 

I know that the "best practice" is to use a single FILTER, but I don't agree with that for this reason.  Please DO use 3 filters and make the biggest CUT first.

 

FILTER1:

[Network Id] in ("a", "b", "c", "d")

use the "FALSE" anchor and connect it to the next Filter

FILTER2:

[Include/Exclude] = "Include"

use the "TRUE" anchor and connect it to the next Filter

FILTER3:

[Service Type] in ("1","2")

connect the "FALSE" anchor to the next tool.

 

I prefer to make the FILTER statements Positive rather than negative for ease of reading.  This has simplified the statement and should get you exactly what you are aiming for.

 

Suppose you had 1,000,000 input records and could first reduce it to 100,000 records by filter 1.  You will be FASTER now because you don't have to check the other two statements.  This could be faster and easier to maintain.

 

Cheers,

Mark

 

 

 

 

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Brad1
11 - Bolide

Sadly, I get the exact same results breaking it out.  Guess I'll keep trying.  Nothing appears to be wrong other than the results.  Running latest version of Alteryx.  I don't get it.

danrh
13 - Pulsar

I think the issue is with the "in" clause.  I don't believe Alteryx allows IN statements.  What I'll frequently do to get around this is use CONTAIN(), i.e. CONTAINS('a-b-c-d-e',[Network ID]) and !CONTAINS('1-2',[Service Type]).  Give that a try, see if works out.

tom_montpool
12 - Quasar

If you break out your filters as suggested by @MarqueeCrew, where does it give you results that aren't expected?

 

The "IN" syntax is looking for an exact match -- therefore if your [Network ID] field is actually "a " vs. "a", the [Network ID] IN ("a") statement will fail but TRIM([Network ID]) IN ("a") would work.

 

This is why multiple filters are great for 'debugging' your processes.

Brad1
11 - Bolide

Honestly, I don't remember how I fixed that or even what workflow it's in out of what seems like countless workflows I have going on.  ...probably something stupid on my part, would be my guess.

Brad1
11 - Bolide

I do believe I got it all in one filter..  Thanks for looking at it and the advice.

JessicaS
Alteryx Alumni (Retired)

Hello all,

 

Just adding a moderator's note to provide clarification here for future folks who may reference. Alteryx does have an IN operator and you can find the documentation under operators within the functions help page

2017-10-19_15-08-21.jpg

 

 

Jess Silveri
Manager, Technical Account Management | Alteryx
danrh
13 - Pulsar

I stand corrected - I thought I had trouble with this before, but testing it out now the IN statement works fine.  Thanks for the links!

Brad1
11 - Bolide

Thx for looking at it.  Somehow I kept playin with it and got it to fly.  Not sure what I changed but it started working.  That was soo many workflows ago. lol actually change that to : (

Labels