Alteryx Designer

Definitive answers from Designer experts.

Counting Records

Alteryx
Alteryx
Created

Scenario  

You have a dataset containing information on customers’ survey responses (Y/N), the Customer Segment (Corporate, Small Business, etc) to which they belong, and other location data.  You have been tasked with finding the percent of each Responder type in the entire data set.  To perform these calculations, we’ll need two types of counts of data.  The first is a “conditional count”, or a count of records that meet certain criteria.  The second is a count of all records in a dataset.  Alteryx has two nifty ways to help us obtain these values.  We’ll use both the Count Records and Summarize tools to help us with these tasks!

 

Methods     

   

Use the Summarize Tool’s “Count” function

The Summarize tool allows us to count the number of records that meet certain criteria.  For our particular examples, we want to find the number of records for each Responder Type, Yes or No.  We’ll use the Summarize tool  to Group by “Responder”.  Then, we’ll Count the number of Customer IDs for each Responder type (Figure 1).

 

Figure 1: The Summarize tool will Count all records, Count NonNull records, CountDistinct (Unique) records and CountDistinct (NonNull) records.   

Summarize_count.jpg

 

Want to drill down in your data even more?  How about find the number of Responder Types per Customer Segment?  Again, the Summarize tool can help!  Group by “Customer Segment”, then by “Responder”, then Count the “Customer IDs”.  See the attached workflow to see this example in action.

 

Use the Count Records Tool

To calculate the percent of each response type for our entire dataset, we’ll need to know the total number of responders in our dataset.  While there are a few ways to go about getting that number, I’ll highlight the use of the Count Records Tool (well, macro, technically).  “The WHAT?” you ask?  I’ve heard that before.

 

The Count Records Tool.  It’s easy to miss and I can count the number of times I’ve seen this tool in a user's workflow on one hand.  However, it’s one of those tools for which you quickly find so many uses!  It does exactly what its name suggests: it counts the number of records from an incoming data stream.  The tool itself requires no configuration.  Simply insert the tool into your workflow and receive a count of the number of records in the incoming dataset, even if there are zero records from incoming data* (Figure 2):

*The Count Records tool will return a record with value of 0, whereas the Summarize tool will simply not return any records. 

 

Figure 2: The Count Records tool has no configuration and returns the number of records (rows) from an incoming data stream. 

CountRecords_tool.jpg

 

Now that we have the counts that we need for our calculations, we're ready to move forward with our data analysis!  Please see the attached workflow for the completed demonstration of this process. 

 

 

 

 

 

 

Attachments