Create Table Summary

[Thess_Luck]

Hi community!

I'm pretty new to Alteryx and need some help with getting an overview of my datasets. My data consists of multiple columns containing different tags respectively. I want to count the occurences of each unique entry in each column and get ONE overview table.

I just created some random data Excerpt.xls.

I achieve to get the summary for each single column very easily with using the GROUP BY and COUNT actions within the SUMMARIZE tool. What I want to get as an output is a table as follows with exactly this count statistic for every column (I just show this here for the first random data columns). I struggle with this. So far I tried some things with transposing the table and using TILE afterwards. But I always run into the problem of counting the entries. Can anybody help me with this? 

Cheers, Thess

amenity	count	fruit	count	highway	count
t	2	apple	1	tertiary	54
a	10	banana	13	secondary	61
c	4	orange	12	residential	229
z	2	strawberry	2	living_street	17
k	7			pedestrian	13
				primary	77
				service	14
				path	3
				footway	4
				unclassified	23
				primary_link	1
				trunk_link	1
				tertiary_link	1

[WesCannon]

Hi Thess,

You can use a 'Join Multiple Tool' and select the 'Join by Record Position' option to achieve your desired outcome. I've attached a a workflow using your Excerpt.xlsx file that creates the desired summary table.

I hope this helps!

Thanks,

Wes

[Thess_Luck]

Thanks for your answer. Your solution solves one part of the problem, I get the table in the layout I wanted it to be. I am just wondering if there is a possbility to run the SUMMARIZE tool iteratively for every column in the dataset without configuring it by hand. Let's say I have a dataset with not only 5 or 6 columns, but 20. Is that somehow feasible?

[WesCannon · Accepted solution]

Hi Thess, it is possible to count the records dynamically for every column in the dataset by using a batch macro. I've created a batch macro that achieves this functionality and layout however one thing to note is the output will have more null values as the batch macro will stack each summarized column. 

I hope this helps!