Validate if input value is an enumerated value.

[yychim]

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.

[Hammad_Rashid]

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

1. Input Data: Bring in your main data file containing the columns you want to validate.
2. Lookup Tables: Bring in all the lookup tables corresponding to different columns.

Step 2: Create a Macro for Validation

1. Open a new Macro:

   • Go to the 'Workflow' menu and select 'Macro.'
   • Name your macro, and click 'Finish.'

2. Configure Interface:

   • Drag in an Interface Tool (found in the Interface category).
   • Add necessary Interface tools like Text Input, Drop-Down, etc., for user inputs (e.g., select the column to validate, choose the validation table).

3. Join Tool Configuration:

   • Bring in a Join Tool.
   • Connect it to the Interface Tool.
   • Configure the Join Tool to join the selected column from your data with the corresponding lookup table.

4. Filter Non-Matching Records:

   • Add a Filter Tool after the Join Tool to filter out records that don't match.

5. Output Tool:

   • Connect a Browse tool to see the filtered data.
   • Connect an Output Tool to save the non-matching records to a file.

6. Configure Output Tool:

   • Name the output file dynamically based on the selected column and lookup table.

7. Save and Close Macro:

   • Save and close the macro.

Step 3: Create a Batch Macro

1. Open a New Workflow:

   • Open a new workflow where you'll use the Batch Macro.

2. Bring in Batch Macro:

   • Find the Batch Macro tool in the 'Preparation' category and drag it onto your workflow.

3. Configure Batch Macro:

   • Connect your main data input to the 'Input Data' anchor on the Batch Macro.
   • Open the Batch Macro configuration and connect the Interface Tools to the corresponding Input Data and Output Data anchors.

4. Configure Batch Macro Interface:

   • Set up the control parameter to iterate over the lookup tables.

5. Run Workflow:

   • Run your workflow, and it will iterate over the selected columns, perform validations, and output non-matching records for each column.

Step 4: Examine Results

1. Check Output:

   • Examine the output to identify records that do not match the enumerated values.

2. Adjust as Needed:

   • If necessary, go back into the Batch Macro or the Macro to make adjustments.

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.

[caltang]

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.

[yychim]

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?

[caltang]

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. 

[yychim]

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.

[caltang]

Try this:

It gives you three outputs:

1. All records are fine
2. Some records are fine, some are not fine
3. All records are NOT fine

Hope this helps.

[yychim]

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

[caltang · Accepted solution]

Like so:

[caltang]

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/