How to create a batch macro with 2 control inputs

Dear Team,

I am trying to extract data from some large tables and i am looking a way to create a Batch macro input that uses variable year and month data in extracting the data.

Year format is "YYYY" while month is formatted as "MM".

Year Structure:

Name	Selection	Operator	LowKey	HighKey	Split
RYEAR	I	EQ	2024		True

Month Structure:

Name	Selection	Operator	LowKey	HighKey	Split
MONAT	I	EQ	01		True

The macro should dynamically change the "LowKey" values for both tables. These tables will then be joined using the union tool and then fed into another tool to extract the data. Then i want this data saved into a folder that has this structure automatically: YYYY > MM > "output.csv". If the folder doesn't exist then it should be created.

For example, if extracting data for 2022, the macro will cycle through months 01 to 12, then move to the next year in the list.

Inputs:

Years (>= 2020): Generated using the "Generate tools".

Month: 01 - 12

Many thanks in advance for your help.

Macros

Batch Macro

Dynamic Processing

Interface Tools

Iterative Macro

Accepted answers

kayogunbo

Marking it as solved. Reached out to the DVW team and they already have a solution that works for my use-case. I should have probably done that from the onset 😅.

Thanks everyone for for the advice and pointers.

All comments

danilang

Hi @kayogunbo

Can you provide some sample input and output data?

Thanks

Dan

griffinwelsh

You can concatenate the two fields to create a single input with a delimiter and then use update by formula to parse your control parameter within your actions that feed off of the control parameter.

kayogunbo

Hi Dan,

This is the input tables table

Name	Selection	Operator	LowKey	HighKey	Split
RYEAR	I	EQ	2024		True

Month Structure:

Name	Selection	Operator	LowKey	HighKey	Split
MONAT	I	EQ	01		True

Final outcome will change the Lowkey for the upper year (1st Table) and another one will change the month data (2nd Table). This data are then now combined into an output macro that is then fed into another tool.

Combined table would look like below:

Name	Selection	Operator	LowKey	HighKey	Split
RYEAR	I	EQ	2024		True
MONAT	I	EQ	01		True

apathetichell

This is with the a DVW SAP Tool - correct? You'll need to use two control parameters - and two action tools - these will feed into the tool's parameters - my memory is that there is a ton of trial and errror in trying to figure out where the selectors are in the tool (there's like 100 different components and things aren't labeled great). I don't have access to a system with DVW or even SAP ECC so I can't really help more than that.

kayogunbo

Indeed. Its a DVW SAP tool.

I dont have a problem with the SAP tools though, just that i need to be able to iteratively change the year and months without the need to manually do it myself.

Considering your suggestion, is there a way that you can provide a sample workflow. I would love to pick a year and run the month input from Jan - Dec before moving to the next year in the list.

Many thanks!

kayogunbo

Marking it as solved. Reached out to the DVW team and they already have a solution that works for my use-case. I should have probably done that from the onset 😅.

Thanks everyone for for the advice and pointers.

LATHROPS

Hey, had same problem and know it is solved, but figured for anyone else out there if they want to give it a go before reaching out to DVW, my solution was thus:

1. Create one macro (will be embedded in other macro) containing your DVW extraction. In this example I am extracting FBL1h. My control parameters are updating the beginning and ending clearing dates. Named this macro subFBL1h_extrct. The incoming data fields are [StartDate] and [EndDate]

2. Create a batch macro containing the sub macro with an input control and a batch control parameter (for the macro, not the sub-macro, that was previous step). The input for this parent macro will be a concatenation of [StartDate] and [EndDate]. I creatively called it [StartDateEndDate]. For testing purposes, when you put in the numeric value into edit data in the Input tool it will default to INT64. You will need a select to take it back to a String rather than having some value like 40520359. Prior to using the macro in the main workflow, add a non-numeric character in edit data within the Input tool to force it to recognize a string input. The Action tool from the Control parameter should be mapped to replace [StartDateEndDate]. Subsequent formula tools will break them apart into fields [StartDate] and [End Date] to be fed into the sub macro.

3. Create your text input file with your [StartDate] and [EndDate] fields with each range. Use Formula tool to concatenate into field [StartDateEndDate] and connect to input data of your macro. Use Summarize to group by [StartDateEndDate] and feed your batch control parameter. Et Voila!

Screenshots below.

Sub_FBL1h.png

FBL1h_extract.png

AP & FBL1h.png

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335