Removing Fields Dynamically

[ned_blog]

We got a question via the forums:

Hi, I'm trying to output to Excel, but a number of fields in my data contain only Null values and I want to exclude these. Is there any easy way to do this? The data changes every time I run the module, so the fields that contain only nulls will not be the same every time. I don't want to have to keep changing the module to suit the data I am inputting. Thanks in advance.

This is another good example of something that there is no direct tool to accomplish, but is totally possible to create a tool as a macro. Thinking about this a second, it becomes obvious that we have to go through the data to figure out which fields to set to NULL. The Summarize tool has a mode for counting the number of non-null fields, but it requires you identify which field ahead of time. Since we want to make it as generic as possible, this doesn't quite work. If we use a Transpose tool 1st, it we can easily group by field and see if there are any non-null records in each field.

Now that we have a list of fields to remove (the fields with no non-null values), how do we do it? The developer toolbox comes in handy here. There is a tool for Dynamically selecting fields, but that doesn't take any data from rows, so how to we get the data in there? Since that tool can look at field names, if we could change the field names to mark them to be removed, our problem would be solved. Fortunately, we have that tool too: the DynamicRename tool. If we take the field names from rows on the right, we simply have to make a formula that renames the field.

Once again, this is an example of something that is relatively easy to do in a few tools. Once a macro is built, it becomes as easy to use as any of the built in tools. The macro and a test module can be found here.