WeeklyAlteryxTips#37 The difference between the count of the Summarize tool and the Count Records tool

Alteryx

I think this is the famous tips.

Which tool do you use the Summarize tool or Count Records tool when you want to count the records? Those tools obviously have the difference, so I would like to explain that.

What is the difference between the count of the Summarize tool and the Count Records tool?

The answer is “the behavior is different when the record count is 0”. When record count is 0, the Record Count will show 0, but the Summarize tool will not show anything. This difference affects the big impact to make the workflow.

User Case : I want to get the count immediately after reading the data

Supposing that you want to know the record count because you know whether the workflow can read the data without error or not. Especially, when the loaded records count is 0, it should be shown as an error, but the Designer’s behavior is not. In this case, you need to use the Count Records tool.

Let’s see the difference of the behavior. The workflow is as follows.

The Message tool is configured to show the error when the record count is 0. The specific setting is as follows.

When there are some data, the result is as follows.

Of course, the Message tools don’t show any erros since there are some data.

Next, let’s load the CSV file which has no data.

Only the Message tool connected to the Count Records tool shows an error. Let’s check the result at the Browse tool.

Certainly, the Coutn Records tool counted though there is no record, but the Summarize tool didn’t count anything.

As the result, in this caes, you need to use the Count Records tool.

Can I get the 0 count at each categories though there are some categories

You can understand that the Count Records tool can show 0 when there is no data. However, let’s see the another pattern. This use case is to count the category which has no data. For example, Category field normally has 2 categories “vegetable” and “fruit”. But in this case, there is no vegetable category data. But you want to have vegetable category as 0 count in the report.

Answer : You can’t do that without any trick.

In this case, according to the first half of this post, you want to use the Count Records tool. But there is no option of the Count Records tool for this case. That tool only can count the whole data. In other words, that tool can’t count the data by each group becaues the tool can’t know what data(category) should be contained.

The solution is that you have prepare the category master data and use it. For example, you can make the workflow below.

On the Input Text tool, you have to prepare all categories with Count 0.

After Union the data, you can have 2 route. One is sorting the data ordered by descend and sample the data, other is summarizing the data grouped by category. In any case, the result is below.

Conclusion

  • Explained the difference between the Count Records tool and Summarize tool.
  • The Count Records tool is able to count no records, but the Summarize tool is not able to count no records.

Sample Workflow Download

The Next Blog Post is…

The next post will be about loading data to the Database using PreSQL.

コメント

Copied title and URL