Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Counting Across Columns

Highlighted
7 - Meteor

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:

 

StockClient 1Client 2Client 3Client 4
ABC.05.010.07
XYZ.060.01.03
123.2.08.060

 

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!!

Highlighted
14 - Magnetar
14 - Magnetar

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!

 

Capture.PNG

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi @cmohyi 

 

The solution is very simple:

 

Solutionagain.PNG

 

- 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,

 

Labels