Hello Community,
I'm currently working with a report that includes various columns such as currency, purpose, customer type, etc. For each of these columns, I have corresponding tables that list the permissible values (e.g., a list of valid currencies).
My goal is to validate whether the input value for each record matches one of the enumerated values in the corresponding tables. I also want to generate a detailed summary about this validation process.
In my research, I've found that to check each individual column in my data against a different table (i.e., each column has its own table of permitted values), I may need to perform multiple join operations. However, to enhance performance and simplify workflow management, it seems a Batch Macro in Alteryx could be a more efficient solution.
A Batch Macro allows the same process (like a Join operation) to be run multiple times within a workflow, each time using a different input. In my case, it seems I can set up a Batch Macro to accept two inputs (my data and one of the lookup tables), execute the join operation, and then output any records that do not match. A Control Parameter could then be used within the macro to sequentially input each of my lookup tables.
While this approach could potentially save me from having to perform multiple join operations, I'm unclear on how to implement the Batch Macro. Could anyone provide guidance or tips on how to proceed with this?
I greatly appreciate your help.
Many thanks.
Solved! Go to Solution.
Creating a Batch Macro in Alteryx for this purpose can indeed be a powerful and efficient solution. Here are step-by-step instructions on how you can implement a Batch Macro to validate each column against its corresponding lookup table:
Step 1: Input Preparation
Step 2: Create a Macro for Validation
Open a new Macro:
Configure Interface:
Join Tool Configuration:
Filter Non-Matching Records:
Output Tool:
Configure Output Tool:
Save and Close Macro:
Step 3: Create a Batch Macro
Open a New Workflow:
Bring in Batch Macro:
Configure Batch Macro:
Configure Batch Macro Interface:
Run Workflow:
Step 4: Examine Results
Check Output:
Adjust as Needed:
This approach allows you to perform the validation for each column using a consistent process without duplicating the workflow. It's especially useful when you have a large number of columns to validate.
I would suggest for you to upload some sample data before proceeding further. Whilst the context is good, it's hard for me to visualize your issue.
I do not see the need for a Batch Macro if your condition is to ensure the list of currencies are as enumerated. Then whatever is not is removed as an exception case.
Hi everyone, thank you for your response.
I believe using a Batch Macro might make the process more complicated. I would like to choose another option by joining multiple tables.
If I have joined multiple tables, what would be the next step to achieve the desired outcome using a multiple fields formula? I want to check if an input value in your report is an enumerated value. For example, I have column A that I want to validate with table A, column B with table B, column C with table C, and so on. If a value violates the rule, I want to revert back the [id], otherwise, it should be null(). Instead of joining the tables one by one and setting up formulas individually, can I use the mutiple fields function to accomplish this?
You could combine your mapping files into one then you’ll join only once.
Again, very hard to confirm if there is no sample data provided.
Hi Caltang, Thank you for your response.
Attached pls find a sample report with mock-up data.
To ensure that the input values in the corresponding columns of the sample report (e.g., currency, frequency) match the enumerated values (Column A) in the corresponding tab of the enumerated value file, what steps should I follow?
I would like to generate a validation report that includes the validated fields, the number of records that have violated the validation, and the details of those records.
I have attempted to join these tables, but I encountered some challenges. As the violated records will be missing, and i cannot do the validation report.
Thank you for your assistance.
Wish you have a nice day.
Hi Cal,
So helpful. If i would like to finetune the outcome to the following formatting as one output report. What should i do? Thanks so much
Validated Fields | No.of Records Violated | Records Details |
Currency | 2 | CustomerID1,Customer ID2 |
Frequency | 3 | CustomerID3,Customer ID4,CustomerID1,Customer ID5 |
Indicator | 4 | CustomerID... |
If the above solved your need, kindly like & mark as accepted solution so that you may help others find the solution more quickly + to close the thread as is. Thanks!
Best regards,
Calvin Tang
https://www.linkedin.com/in/calvintangkw/