Total Row column (360 columns to sum)

Question

Hi all,

I'm currently trying to design validation workflow which creates various error messages based on a multitude of requirements. One of the checks I have is to make sure there are no negative values across 360 different string columns for 750k+ records.  Of course an easy solution for this would be to utilize the CReW macro pack and use the Total tool, but in order to maintain streamlined usability across multiple users who are not familiar with Alteryx we decided to not have others download material from the internet in order to hit run once.

So as of right now, I had a multi-field formula doing a bool check on whether the cell contains a '-'. Then I have the bool values grouped and summed by the source file and columns. Now my issue is I do not want to output a report with 360 fields for 12 different source files when this could easily be 2 fields, one for the file and the other for the total negative values. Now all I have to do is add the 360 fields up into one column. I've run into this issue years back and we ended up just using the CReW pack, but is this really the only solution? I can just use the formula tool to create a new field and manually enter all 360 fields to be summed, but that would take forever and just doesn't feel right. Is this my only potential solution? Am I missing something with the multi-row or multi-field functionality?

Thanks in advance

DataNath · Answer

You could use an approach like this @boostman.

First, we transpose the data so we just have a single column with all values (any fields you DON'T want to bring into this column to check i.e. genuine string fields etc, make sure to tick as Key Columns):

After making the [Value] field numeric, we then use a summarize to find the minimum:

Then use a message tool to shout at us if the minimum value is below 0:

If you need the actual count of negatives in the data then you can also filter out negatives, perform a count and build that into the message:

Flag negatives in data.yxmd

IraWatt · Answer

Hey @boostman,

You should never have to SUM up 100's of fields in a formula tool. What you will want to do is transpose your data values into one column and use a summarize on that one column. Could you give an example data set and desired outcome for context.

Thanks,

Ira