Filter Help

[Brad1]

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.

[MarqueeCrew · Accepted solution]

@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

[Brad1]

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 · Accepted solution]

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 · Accepted solution]

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]

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]

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

[JessicaS · Accepted solution]

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

[danrh]

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]

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 : (