Batch Macro update date

[omkarshinde]

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)

[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 @binuacs 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.

[binuacs]

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

[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 @binuacs 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

[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.