Filter Based on Separate Dynamic File

[kygould]

Hello,

I am trying to build a workflow where sales data (revenue file) is filtered according to a list of products in a separate file (filter file). The filter file will be a live file with the list of products changing regularly. I know a join can act as a sort of filter, but the problem I'm running into is that the list of products has multiple filter criteria, and they are not consistent in format - some criteria are very specific, while others are more broad. Here's a dummied-up example:

A company makes boxes in the following variations:

• Paper, Plastic, or Cardboard
• Red, Blue, Green, Yellow, or Orange
• 12x12, 18x18, or 24x24 

Sample filter and revenue files are attached, as well as a file showing the end result I need to get to.

I suspect assigning a "filter number" to each specific set of criteria in my filter file will probably be necessary, but even with that I can't seem to figure out a way to do this. 

Thank you in advance for any advice!

[CeliaC_Silje · Accepted solution]

Please see if the attached workflow file is the solution you are looking for.

Your idea of assigning a "Filter Number" is correct - it is just a bit complicated to identify whether a row of sales data is matched with the criteria of a "Filter Number".

I also assigned a [RecordID] to each row of Sales Data for easier identification and joining.

Instead of matching all criteria at once, the workflow matches the criteria one by one and append the [FilterID] for each matching criteria.

After that, the workflow will count the matched criteria by each combination of [RecordID] and [FilterID].

This step is to identify whether a Sales data row (RecordID) matches all the criteria of a [FilterID].

The revenue of a RecordID is included only when all the criteria of a [FilterID] is matched.

Then, the workflow will summarize the Revenue of each [FilterID] and append back to the Filter List for the final output.

I have also included annotation for most of the steps in the workflow, and it should help you to understand the whole process.

[kygould]

This is amazing, thank you! It does exactly what I need.

I was able to apply it to my actual data, but there is one small section I have a couple of questions about:

What is connection #2, and what is the purpose of this union?

Thank you!!

[CeliaC_Silje · Accepted solution]

Data from Connection #2 is the Filter Criteria that does not have any Revenue data appended, i.e., none of Sales Data matches to these Filter Criteria.

(If you click on the L anchor of the Join tool after running the workflow, you would see those Filter Criteria.)

I added the Union tool solely because I see that in the Sample Result.xlsx, you also include the Filter Criteria that does not have any revenue.

So if you don't need to show those Filter Criteria in the output file, you can simply remove the Union tool from the workflow.

[kygould]

Ah, got it. Thanks again, this is so helpful!