Filtering Columns by Tab using Basic Table and Layout Tools

[Alyssa]

Hello Community!

I have created a basic table in Alteryx using the layout tool to separate data into tabs based on entity name. I then put this into a render tool and write to excel. The issue I am coming across is that each tab includes the same columns, even if some contain only zeros.

I’ve attached a basic version of my workflow for reference. Does anyone know how to keep only the columns with numeric data in each tab and remove those that contain all zeros using the basic table and layout tools? In my actual dataset, I have approximately 30 entities, and this number will continue to grow. I would prefer not to create 30 separate tables for each entity.

I have also attached a screenshot showing the columns I would like to have omitted from the output.

Thank you for your help!

Alyssa

[KGT]

If you want a different format each time, then you will need a different config on the tools each time. In your case, it would need to be before/during table generation, as once that is done, the table is now your object and to edit it would involve editing the HTML (which is not the path you want).

This leads straight to a batch macro.

So, in your case, and this may differ slightly depending on your actual use case if it is more complicated than your sample, you just need the table in the batch macro, but adding in something that allows you to remove columns that are zero. My easy way for the inside of this batch macro is to transpose (just the states), filter to remove zeros and then cross-tab and table. Then return that through the Macro Output. The control parameter will be the entity.

Then you will have a table object for each entity and you can treat that as one consistent column, even though inside that column, they are all different.

[ntakeda]

Does this mean "Delete the field if all values are 0"?

Step 1: Replace all 0s with null using Multi-field.

Step 2: Remove columns that contain only null values using Data Cleansing. Restore null values back to 0.

 

I’m attaching the workflow.
Please check for the detail.

[Alyssa]

Thank you for your response! I apologize for not being more specific. I would like to remove columns from the data that contain no information or only zeros. Please see the screenshot below for the columns I want to exclude from the final output. Hope this helps clarify!

[Alyssa]

Would you be able to provide an example? I tried to go the batch macro route and failed to do so successfully.

[KGT · Accepted solution]

Find attached, the batch macro. Note that I have selected "Union by column name in the Macro Interface Designer settings, but I think that's the only real option that is selected.

Group By the Entity to get a list of Entities. Use that as your control Parameter going into the upside down question mark.

You may need to play with the settings in the table tool inside the macro to adjust size etc...

[Alyssa]

This is the closest I have gotten - thank you! There are two issues I am coming across - the first is that the columns (which are states) are not in alphabetical order. The second issue is that there are ID items missing as well. I want to include all rows, just filter based on the columns. I hope this makes sense. Thank you!

[KGT]

The columns should be pretty easy to sort but I thought that would happen automatically... probably a sort after the filter and before the cross-tab (make sure the sample data in the macro input has all states). 

As for the missing rows... yep, I can see how that would happen. Got to either enforce the original rows or test for the rows taken out. I would always just enforce the rows using a join to see what was lost and add back in.

I've updated with those changes. Hopefully it all still works as expected. You'll see my commentary, and I changed the input data in the Macro Input to force some errors.

[Alyssa · Accepted solution]

I was able to figure it out using your initial macro as a reference! My actual workflow is quite a bit more complex, so I had to make some adjustments. Thank you very much for your assistance!