Dynamic Filter question

Hi Community,

Is there a simple way to filter from my data set with set of conditions?

Example, below Input data set contains 5 columns. I would like to return the highlighted rows where conditions are met.

Conditions are source from separate worksheet with identical column fields header (refer second screenshot). * means can take any data from that column field.

Many Thanks for the help.

Datasets

Best Practices

Dynamic Processing

Accepted answers

lisaac

Thanks for taking the time to develop further Storm. Much appreciated!

I can see that the key logic is in Formula(28):

This is where I had previously believed you had to treat Strings and Data separately, but your logic shows that you can perform integer tests and math operators on sub-strings. The logic clearly works and, if there's no risk in taking this approach, it opens up my eyes to a whole new range of analysis options. So thanks for that.

One suggestion I have is, for obtaining the number of criteria, you could use the Field Info tool, followed by a Summarize on Count of Name?

All comments

BenMoss

I think it's possible to create something that does this but I don't think it's a straight forward example.

@jchan58 could you share the data as a file so we can look to build examples for you.

Ben

lisaac

My first response to a forum problem, so I'd welcome any pointers where my suggestions could be improved. The first solution is for when the conditions are all strings and just looking for a match (I'm ignoring the sales data). The other caveat is that this will only work if the number of conditions remains the same (I don't know how you can add unknown fields to a Find Replace append).

Just Strings.JPG

To find which records meet all criteria for any of the conditions, I'm filtering on a total sum of 4 matches for each condition.

Because I'm transposing the data into a single column, I think you have to treat strings and data separately, which is what I've done in the second workflow.

Strings and Data.JPG

The process is duplicated for data and joined using a Union. This time I'm filtering on a total of 5 for each condition, which shows that records 3,4,5 and 7 meet condition set 1, record 6 meets condition set 2 and record 10 meets condition set 3.

You can then refer this back to your original data records. I think this works fine if you can fix the number of data fields and number of conditions, but I'd be interested to know if anyone has ideas on how to include variation in these.

Just Strings.yxmd

Strings and Data.yxmd

Storm

@lisaac:

I like your solution.

I accepted your invitation to try to make it more dynamic and general-purpose. I THINK I am there with the attached workflow.

I attempted to overcome the whole "string versus numeric data" with the following:

I convert everything (both inputs - data and rules) to v-string first (length 500 but you can make it whatever)
In order that we don't have downstream issues with workflow thinking any store with number greater than 5077 passes the store criterion, I added a single change to the incoming data: on the rules input, any field which is a number and should only consider a GREATER number as passed (e.g. total sales), the field must have a ">" sign added to start of field (see the workflow for illustration). This is the signal to the workflow that "this is a number field AND the rule is that the number in the data must be greater than this one". If it's a number field that's intended to be used as exact matching criterion (e.g. "866" as store number), you don't use the > on that field / record.

I attempted to make the solution more dynamic by with the following:

Checking various "dynamic or unknown" type of toggles on various tools
The bottom-most part of the workflow captures the number of matching criteria in our ruleset; and the downstream workflow then checks to see if any record matched THAT number of criteria in any rule, versus the hard-coded 5 associated with OP's initial 5 criteria. Allowing for any number of rules, so long as the OP's problem statement (same fields in data and rule set) is intact.

I've run a few tests including adding more records to the data; more fields to the data and rules, etc, and seems to work ok. I'm sure it could be improved further; but seems fairly dynamic and workable.

Alteryx_solution_20180701_dynamic_matching_v2.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703