I am trying to filter my data with the IN function. I want to check that my field CODE is does not contain any value from a long list of possible codes. Instead of manually writing down a 10 mile long "[CODE] NOT IN ('US9841983','JF98324723,'JI83872920'....) expression, I just want to use a specific field that contains all those values. I tried creating that field by concatenating my data into a single field with comma as separators and ' at the start and beginning of each value, but when I tried to use the field in the NOT IN expression of the filter tool, the results were not as I expected.
Can someone share a working example below of what I am describing? My original data looks something like this (but a lot more codes)
Code | Column2 | Column3 |
US874932294 | Random data | Random data |
JI9237820283 | Random data | Random data |
IF8201827332 | Random data | Random data |
KS898720923 | Random data | Random data |
IK8297192902 | Random data | Random data |
OK092832910 | Random data | Random data |
And i am trying to create a field that can be used in the argument of the IN function so I don't have to write down 200 different codes.
Solved! Go to Solution.
If you are searching for text, I'd use a find replace tool and append the found term. Then you can easily identify null versus the first term found as IN.
cheers,
mark
Thanks to both for the suggested solutions, they do achieve the exact same thing as what I'm looking for, but just for the sake of curiosity, is it possible to do with the IN function as I'm describing? @MarqueeCrew @atcodedog05
Hi @AkisM
Using IN no its not possible. Instead you can use contains like below. Which should the required action.
Contains([List], [Value])
Hope this helps : )