Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Using Frequency Table in Iterative Macro

aHogancamp
7 - Meteor

I am building an iterative macro that can dynamically clean data from a raw data set for predictive analytics, but as part of it, I would like to de-select fields that contain exactly 1 distinct non-null value (or said another way, only select fields with at least 2 distinct non-null values). To do this logic, I'm using the Frequency Tool in combination with concatenation aggregations, filters, and dynamic selects to figure out which fields to keep and which to remove.

The macro internally works like it is supposed to using the macro input file I used to build it. However, my problem comes from trying to use the macro and push a new data set into the macro (externally from the macro); any other data set I try to use yields null outputs. After putting output files everywhere, I've narrowed down the problem to be defined as the following: the Frequency Tool does not seem to be dynamic enough to be used in a macro. It can't handle new fields and yields only null values because it looks only for the macro input field names and doesn't allow the new data set field names to pass through.

 

Am I doing something wrong, or do I need to try to dynamically update the XML for the Frequency Tool somehow? This feels like it should have already been solved, but maybe the frequency tool usage is a new case.


Thanks in advance for any help!

7 REPLIES 7
patrick_digan
17 - Castor
17 - Castor

@aHogancamp I think there are a couple of options:1) You can right click on the frequency tool macro and "open macro". Inside there, you would want to change Transpose (10) to check the dynamic or unknown fields. Then you would want to delete the action tool and list box tool. 2) I've attached a modified version of @Ned's remove null columns macro. The concept is to to transpose the data and then summarize it looking for distinct non-null values. This macro is very straight forward and should hopefully accomplish what you're looking for!

pcatterson
11 - Bolide

I'm not that familiar with the frequency table tool directly, but I believe the first step of your batch macro could rename the relevant field and then the final step could rename it back to the original name.  This approach would logically get around the problem, but I haven't tried.

aHogancamp
7 - Meteor

@patrick_digan Thank you for your quick response! I did as you suggested, and copied and pasted those tools from inside the Frequency Table into a new workflow as I'm not sure I want to start adjusting the standard pre-made macros - I selected the "Dynamic or Unknown" checkbox in the transpose as well as deleted the action tool and list box tool. However, whenever I try to utilize the new macro "FrequencyTool_MacroVersion", I keep getting a FileNotFound error because it keeps looking for this macro in the pre-made section and my custom macros are found elsewhere. In the assets tab of the configuration window, both file paths are showing up as auto-detected (the new custom macro location and the original pre-made macro location). 

Did I do something unexpected?

 

Thanks!

patrick_digan
17 - Castor
17 - Castor

@aHogancamp Can you copy the attached into your custom macros and try it? I just changed a couple more things: I changed the category so it's not on the data investigation tab. I also deleted the couple of tools that generate the nice graphs coming out of the I output (which were causing the file not found error). Then it seemed to work for me.

aHogancamp
7 - Meteor

This absolutely worked! Thank you so much! How did you change the category? And how did you find out that it was the graphs throwing that error? I'm trying to learn to be a very serious Alteryx developer, so I'd like to know how to manage these things in the future.

patrick_digan
17 - Castor
17 - Castor

@aHogancamp My answers on the community are admittedly way too brief! I'd be glad to expand on those items. 

 


@aHogancamp wrote:

How did you change the category? 


When you have the macro open, you would click on the Meta Info tab and then change the category name. Erasing the category name will put the tool in whatever default category you have when you setup your macro repo:

Capture.PNG

 


@aHogancamp wrote:

And how did you find out that it was the graphs throwing that error? 


I made the mistake of not testing my solution before I responded to you. When you indicated that it was having an error, I tried it myself and this is what the results showed:

Capture.PNG

 

The frequency table macro is reporting an issue with Tool #77. When I opened the macro and used ctrl + F to find tool 77, it was an input tool. It looked like there were a couple tools that were all leading to the I output of the macro (which were just graphs), so I just deleted them all and hoped that you didn't need the graphs for your specific purpose :) 

jonriddle
8 - Asteroid

Thanks for this!  I'm using the Field Summary tool and found that it doesn't include boolean.  Using the dynamic select to get all boolean fields and then using your modified frequency table tool, I can get the frequencies of True/False values.  Much appreciated!

Labels