Dynamic filtering data with condition from another file

Question

Hi all,

I am struggling with a filtering need in my workflow.  I try to apply a filter to an input file of transaction. I would like to be able to filter in this file, data for a specific team with specific information.

For that, I have another file where each row correspond to a team location (one team can have several location) with the type of transaction and code transaction that will be use to filter the input data file.

I have prepared two example files (as data of my real files are confidential), but I haven't found the right way to do it yet (Had a look to dynamic tools, join+ filtering, etc.)

The idea, is to avoid to change the Workflow in future each time there is change in the team (new team, new type of transaction, etc.) and just an update of the Excel filtering input will be necessary to do.

The goal is to obtain an output file for each team/location with all the transaction related to them from the data file input. I know that if I had to do that on only one team, I could just use a Join tool with the type and code transaction, but there is more that 80 different row team/location to filter.

If anyone have an idea on how to do that, it would be very helpful.

Data-transac-input.xlsx

Filter-condition-input.xlsx

DataNath · Answer

Forgot to add this to my previous reply: In terms of dynamically bringing in the list to apply this, you could do this by just fetching your filter file from a certain directory, like so, so that when you make changes to the list in Excel, this will be brought in on the next Workflow run:

Dynamically bring in list for batch macro filtering.yxmd

Doc-blue · Answer

HI IraWatt, thank for your answer,

The idea is that, for each row in the "Filter-condition-input" file, the workflow filter in the "Data-transac-input" all the transactions that correspond to the team (row).

It will need to filter the type of transaction and the code of transaction and will create an output with only the transaction that correspond to this team (row in the filter condition file) where we will see the Type and code of the transaction but also, all the information about this transaction (amount, date, etc) that we can see in the Data-transac-input.

As you can see in the filtering input, sometimes, we take ALL the transaction for a specific type of transaction, but sometimes, we take a type of transaction but also a specific code of transaction (most of the time, it is a list of code).

Example, ligne 11, I need to take all the Ail type and transaction code 000, 555, 666 and 222 only.

And it will have to do that for all the row, with one output for each row.

DataNath · Answer

If I understand the question right, it sounds like you could do this using a batch macro.

You would just need to feed your list of unique teams/locations into the upside down question, which would then update your control parameter.

What this will do is then run an iteration of the workflow, replacing the filter each time with the team/location with the next in the list and then outputting that.

There's a good thread on how to build these here: https://interworks.com/blog/mtreadwell/2015/11/20/control-parameters-and-simple-batch-macros-alteryx

Likewise here with a YouTube video: https://youtu.be/NNH_K5U5r78

If this doesn't sound suitable then let us know of course!