CrossTab – Count string field

Question

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

Dept 1

Group B

1

Dept 2

Group A

1

Dept 2

Group B

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

ScottC_00 · Answer

Worked like a charm.

Thanks

StevenP · Answer

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.

Crosstab Count String Field.yxmd

geraldo · Answer

@ScottC_00

an workflow example

CrossTabe.yxmd