Hi all,
I'm trying to find a dynamic way to count the number of specific values across columns. Here's a sample of the data:
Stock | Client 1 | Client 2 | Client 3 | Client 4 |
ABC | .05 | .01 | 0 | .07 |
XYZ | .06 | 0 | .01 | .03 |
123 | .2 | .08 | .06 | 0 |
I'd like to create a new column with a count of the instances of 0. I don't want to use the formula tool because my column headers have account numbers rather than Client 1, Client 2, etc and those account numbers will change regularly. I think the multi-row formula or multi-field formula might help but I haven't been able to get those to work.
Thank you all very much in advance!!
Solved! Go to Solution.
Hey @cmohyi
I have attached a workflow that should get you the result you're looking for. I added a couple rows and changed the data a little bit to try and account for different circumstances.
First transpose your data, use a Summarize tool to count the instance of each value for each stock for each name. Use the Filter to only keep the 0 values. Use a Cross Tab to get the data oriented correctly again. Lastly use a Join then a Union to get your final output.
Hope this helps!
Hi @cmohyi
The solution is very simple:
- The secret when you want to dynamic Summarize something is to transpose your data.
- Transpose data (Key Field should be Stock, I assume) - (Data Fields - Check Dynamic/Unknown Fields)
- Create a Flag to indicate if your Value field is 0 (Flag 1 or 0)
- Group by Stock and Sum the zeroes
- Join it back to the dataset
WF appended.
Cheers,