Batch Macro, Cohort of Files from Multiple Directories, One output per Cohort of files.

[AndrewSu]

Hello Community,

Trying to see if anyone can help me with a batch macro idea/issue.  

I have 3 folders with multiple files in them.  We'll call them Folders A, B and C.

I need THIS MONTHS data from Folder A and Folder B, and i need LAST MONTHS data from Folder C, all for the same specific Account ID

These three files are then the inputs of a process.

I want to output a file for EACH COHORT of files/account ID. 

I know I need a batch macro of some kind, but wanted to see what other people's thoughts were. 

Attached are sample folders/directories. 

[Qiu]

@AndrewSu 
Maybe I am missing something here, but we can apply wild card with Data Input tool as below.
From there, since we have the full path along, we can some filter onwards.

[BrandonB]

This will do it. Wasn't sure which folders you wanted to pull the latest month vs prior month for, but you can easily tweak that by routing the folders through the filter tool accordingly. Workflow attached:

[BrandonB]

At the end, you could use an output data tool where you take file/table name from field and use the account column to dictate how you would like this to be produced. 

[AndrewSu · Accepted solution]

Thanks @Qiu and @BrandonB. Your posts definitely got me thinking about the solution in other ways!  

At the end I made a few batch macros with dynamic filters that solve the issue.  main workflow is below.  Notice that the Control Parameter for each macro is connected to the same text input tool (which is a list of Account numbers). 

Macro #1 is looking for this months appropriate file from the 2 directories based on account number, which i parse via regex from file name and I dynamically filter based on the following expression within the filter tool.  

"datetimeformat(DateTimeNow(),"%b") = [Month]

and [Year]=datetimeformat(DateTimeNow(),"%Y")

and [Account]="111""

This allows the workflow to always pull in the current month file info based on the current time the workflow is run. This is done for each Account Number which is the input to the Batch Macro Control Parameter.

See Macro #1 below. 

Macro #2 is identical to Macro #1 except it is looking for the last months data.  The expression in the filter tool updated to the following. 

"datetimeformat(datetimeadd(DateTimeNow(),-1,"month"),"%b") = [Month]

and [Year]=datetimeformat(DateTimeNow(),"%Y")

and [Account]="111"

Notice the orange portion of the formula is what changed from Macro #1 to Macro #2. This is done for each Account Number which is the input to the Batch Macro Control Parameter.

Macro #3  dynamically reads in the data that is identified by Macro #1 and Macro #2 and spits out one file for each account number.  The process in this macro is run for each Account Number which is the input to the Batch Macro Control Parameter.  see below. 

For now, the partner believes this is the solution that makes sense for their set up and end goal.  Crossing my fingers hoping it is!

- Andrew