CrossTab – Count string field

[ScottC_00]

I need to count a string field to create a crosstab table

I have a dataset like this

Employee ID	Department	Group	Job Level
A1111	1	A	Pres
A1234	1	B	VP
A4567	1	A	VP
B6584	2	B	Prof
B7894	1	A	Prof
B3268	2	B	Assoc
C9638	2	A	Assoc
C5487	3	B	Prof
C2169	3	A	Assoc

I want to create a crosstab table that has Department and Group as the rows and Job Level as the Column Names and I want to know how many Employees are in each cell (I want to count Employee ID).

		Pres	VP	Prof	Assoc
Dept 1	Group A	1	1	1
Dept 1	Group B		1
Dept 2	Group A				1
Dept 2	Group B			1	1
Dept 3	Group A				1
Dept 3	Group B			1

I tried using the Crosstab tool but I don’t think you can count string fields using that tool.

I also tried using the Summary tool but I think it has the same problem.  Does anyone know how to create a crosstab (Pivot like table) using the count of a string field as the value?

Thanks

[geraldo]

@ScottC_00 

an workflow example

[StevenP]

Hi @ScottC_00,

This can be achieved through the use of the Summarize and Cross Tab tools. First group by the relevant fields (Department, Group, Job Level) and then add in Employee ID using the Count option. Afterwards insert a Cross Tab tool, group by Department and Group, change column headers to Job Level and set Values for new columns to Sum of Count. From there you can play around with the formatting of the fields / orders through the use of Sort, Select and Formula tools.

Summarize configuration

Cross Tab configuration

See attached for an example workflow using your provided data.

[ScottC_00]

Worked like a charm.

Thanks