Batch Macro update date

I have multiple filter and formula tool in workflow with date which has to be updated; basically first date of every month for every consecutive run. So I am using control parameter and update value action to do this. However batch macro is unionising records of output after every run which i dont want is there a way to achieve this? (the date was being changed manually earlier and now it is changing dynamically so a lot of calculations in the workflow are based on earlier flow that why i dont need the union part to be happening)

Workflow

Output

Macros

Apps

Batch Macro

Dynamic Processing

Iterative Macro

Accepted answers

All comments

caltang

Please provide relevant data to this use case, and kindly provide your criteria in as much detail as possible. If you have a workflow built halfway, kindly export that over as well. To export a workflow go to: Options > Export Workflow. Kindly do NOT send a "Save As" copy.

omkarshinde

since the data is sensitive, I wanted to ask is there any way in which batch macro can be run but without unioning the output results that is with every run i want the results of that run only and not that of the previous run attached with it!

caltang

Not sure why you need a batch macro... why can't you just group by the months and get min values of dates to get the file of your liking? I have trouble visualizing your request - hence the reason for providing data / workflow. If the data is sensitive, the least you can do is provide dummy data relevant to your use case!

It helps us visualize better - who knows, we may provide a better solution alternate to what you're doing.

omkarshinde

So this is one of the filter tools and similarly formula fields are present in the workflow and I need to pass dates like 2023-01-01 for first run, 2023-02-01 for second run, 2023-03-01 for third and so on until last month however this process needs to happen on its own dynamically without me having to change the date at every tool for every run!

binu_acs

@omkarshinde @Batch macro works similar to union tool, what is the expected result you are looking for Once it read the first batch of records ?

omkarshinde

I just want the workflow to run for each first date of month that is 2023-01-01 for first run 2023-02-01 for second and so on until we reach current month. However I have many calculations based YTD MTD level after this thats why i dont want the union part of batch macro! @binu_acs @caltang

binu_acs

@omkarshinde one option is to write your all calculations inside the batch macro, then let it union your results, once you have the final output you can do the rest

ChrisTX

It's difficult to provide a suggestion without understanding the *full* process. In a case like this, a sample workflow without sensitive data may be the only way to provide a definitive solution. Otherwise, we're just asking more questions.

You mentioned...

I need to pass dates like 2023-01-01 for first run, 2023-02-01 for second run, 2023-03-01 for third and so on until last month

What happens after the macro call? Do you generate output files? Since you don't want the output Unioned, would a Standard Macro work?

Chris

omkarshinde

I tried putting all my filters an formulas as told by @binu_acs inside the macro to prevent union of records however now the dates of macro output arent changing they are just 2023-01-01 even though i have given 2023-02-01 too along with it in the dates files.

My only ask is that the dates file containing my dates such as 2023-01-01, 2023-02-01 should be changed at required formula and filter fields wherever i have attached update value and control parameter tools.
I have tried to reference my input and required output files inside the macro itself however the inside output files are remaining blank. and macro output is giving non unionised output but with non change of dates.

binu_acs

@omkarshinde The batch macro should pick all the dates if you configure properly, attaching a sample workflow for your reference

Sample_121123.yxzp

apathetichell

My read of this is a poorly described problem which occures where after the filter in the original version there is a section where you change your column name to reflect the date. Your macro outputs via a union. You now have a series of rows which have blanks where the date is not relevant. So you have something like:

items quantity 2023-01-01 2023-02-01 2023-03-01 etc... - and you have multiple nulls afte reach run.

you fix this with a summarize tool in your MAIN workflow - after your bacth macro. You group by your key columns (item/quantity) and use MAX value - for your other columns.

if you want to make this dynamic - you transpose everything but your key columns - group by name, and then take the max value. after that you have to do some nifty crosstab and then a dynamic rename where the original field headers become your field headers again.

That's how I'm reading your issue at least.

omkarshinde

Hello @apathetichell I have tried to put a sample workflow with some sample data and as suggested by @binu_acs I am trying to keep all my calculations inside my macro. My only ask is that i want my dates to change dynamically after every run and also run on its own after change thats why i am using a batch macro. Also i dont want any kind of output from the batch macro

Capture.PNG

apathetichell

post your workflow and some sample data - your are doing something else wrong. either your action tool config -or how you are feeding in your data to your control parameter, or your underlying data is wrong.

also - if the same value is going into the control parameter - you need two action tools - but only one control parameter. one control parameter has a one to many relationship with action tools.

omkarshinde

I am unable to post workflow due to my firm restrictions however i have tried to replicate my situation wit h sample superstore so there are no null fields in date.
I got your control parameter value value point fixed that for now.
Nothing is solving the question of how to use batch macro without making it unioning after every iteration. Or what else to use to dynamically change dates and run if not batch macro ?

CoG

There is no way to use a batch macro without the Union since that is how a batch macro works. It takes in data, "batches" that data via a control parameter, manipulates the data in batches (so that each batch can be treated a little bit differently, as required), and the output of the macro is the union of all those batches. Otherwise, you now have all those batches as separate data streams that you have to manage yourself independently, which doesn't make any sense (imagine adding a new input tool every time you want to add a new file to your workflow, that would very quickly become unmanageable).

That being said, the data that has been Union-ed is still distinct by batch, so all your calculations would be unaffected and ready to be output/displayed however you see fit. This is one of the points of confusion that I believe many have in this thread, and why specificity will be beneficial in getting the proper assistance. This is made more difficult by firm restrictions, which is understandable, but how can your problem be simplified?

For example, You have two columns

Date	Count
Today	1
Today	3
Yesterday	2
Yesterday	7

For each date, you want to do fancy calculations (simplified to getting the sum of all counts for this problem), and output each set of calculations to a separate file/sheet.

^Something like this is simple, clear, and conveys exactly what you need/what the problem you are trying to solve is.

apathetichell

@omkarshinde you are not describing this well. Sorry. A batch macro unions ON OUTPUT TO THE MAIN WORKFLOW. that's it. If you are writing to a document - change the name of the document on each run using a formula tool (with an action tool feeding it). I do not know where you are seeing the union issue if you are not brining this into your main workflow - unless you are trying to do two things in one macro:

1) read in a multiple sheets from a file.

2) output a single sheet from the same macro with the same file combo.

that will not work. you will need two seperate macros - one to read in and one to write out. (or one to read in and create the dynamic file naming logic in workflow). if you can mock up some data and take a screen grab of your data issue maybe someone can help.

omkarshinde

The issue with batch macro unioning is i have my own union logic in the workflow which needs monthly data for monthly level calculations and YTD data is also referenced whenever YTD metrics are referenced. Batch macro union is causing my final output to produce duplicated records. Also my input and output file is same and it needs to be referenced like more than 20 times in the workflow.

CoG

I will provide a few of my own guesses based on what's been shared thus far as possible workarounds/solutions. It does sound like your workflow is complicated, which is why being very specific will help and even providing a sample input and output (maybe even 2 outputs for what you see vs. desired output) with dummy data would help clarify things further.

Case 1: Batch macro is outputting duplicated results that are unexpected.

Possible Solution 1: You need to use the Group By fields in the Batch Macro interface (this assumes that you were not previously)
Possible Solution 2: You are doing some kind of redundant calculation that shouldn't be a part of the batch macro. Just take that logic outside of the batch macro. I honestly cannot think of a reason this would be happening that would not be blatantly obvious silly.

Case 2: Batch macro is not outputting duplicate records. But duplicate records are still being produced.

This too doesn't make sense to me. After the internal union (final step) of a batch macro, every record in the output can be matched to exactly 1 batch from the input. This would imply that the batch couldn't be your source of duplication, unless you are running the batch multiple times for some reason and then else where in your workflow, using the Union Tool.
If this case describes your problem, please describe what's going on with simple details to illustrate the issue.

Case 3: This has become a bigger problem than it ever needed to be.

Possible Solution: Since you were just running this workflow repeatedly in the past without issue and manually changing the dates, just make the entire workflow an iterative macro (See attachment/images for details).

2023-11-15_07-49_Iterative Macro2.png

2023-11-15_07-49_Iterative Macro.png

2023-11-15_07-49_Iterative Macro.yxzp

apathetichell

@CoG - one addendum to your excellent summary - for Case 2- the problem could be the CONTROL PARAMETER feeding into the batch macro is not unique. In this case the batch macro would execute multiple times for a give value. The easy fix for this is drop a SUMMARIZE tool prior to the batch macro in the MAIN WORKFLOW and group by the CONTROL PARAMETER.

omkarshinde

I want my output file to be updated after every run because it goes as an input in next run. So i think i will need a combination of both batch and iterative macro to achieve this!
The control parameter will take care of the changing of dates dynamically in all formula and filter tools wherever required and iterative macro part shall take care of updating the output part after every run making it ready as an input for next run.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335