Filtering Zero Sum Columns when Outputting file by ID Field

Question

I am looking for assistance on how to output a workflow that will create a file for each change in a location ID and will output only columns for that location where the total total sum of that column is not zero. I have attached test workflow showing how I currently have this configured for accounts receivable data. I have a list of customer ID's, by location with an AR balance by year. I am looking to report the AR balance for each customer where a new file is created for each location, but using the process below it will show all zero for a year value if any location in the source data has a value for that year.

In the attached image the Chicago file has values for all years, however the FT Wayne file does not have values for 2021 & 2022 so these columns show as all zeros. Is there a way to remove these zero sum columns when the file is being written? I could filter by location and then set the zeros back to null values and then use a data cleanse tool to remove the null columns but I am looking for a more dynamic way to do this as there are many locations in the source data and I don't want to maintain the process if a new location is added.

Thanks in advance for any and all help!

Chicago.png

FT Wayne.png

Test Data.xlsx

Loc AR Zero Issue.yxmd

Emmitt18Lefebvre · Answer

Hello!

To dynamically create separate files for each Location ID containing only year columns with a non-zero total AR Balance for that specific location, group the data by Location ID. Then, use a dynamic method (like a Formula tool to identify non-zero sum years combined with a Dynamic Select tool, or a Transpose-Filter-Summarize-Join-Dynamic Select sequence, or a Python script for advanced logic) to select only the relevant columns for each location group. Finally, use a Dynamic Output tool to create a separate file for each Location ID, ensuring only the Customer ID and the non-zero sum year columns are included in each file. This approach automatically adapts to new locations and year columns without manual updates.

SPetrie · Answer

Because your data is all going through one output, a year column will show up for all regions even if it is only filled in for one region. You would need to use a batch macro to take each region individually and create output for them one at a time.

I added a summarize and join to filter out regions that have a 0 sum and then pass that to the macro. The macro is the tools from your cross tab onward.

Loc AR Zero Issue.yxzp