Creating column names during transpose

LincolnMike

Hi, 

I have the following data and attached a Workflow.

Act	unit	Status
123	abc	C
567	abc	O
123	def	O
567	ghi	C
567	jkl	C
789	mno	C
890	mno	O
789	pqr	O

I use Summary and crosstab tools for this display:

Status	unit	123	567	789	890
C	abc	1
O	abc		1
O	def	1
C	ghi		1
C	jkl		1
C	mno			1
O	mno				1
O	pqr			1

But what I need is the Status to display with the Act field, including if there are no values, like the following.  Any Ideas?

Unit	123_C	123_O	567_C	567_O	789_C	789_O	890_C	890_O
abc	1			1
def		1
ghi			1
jkl			1
mno					1			1
pqr						1

FieldNames.yxmd

BenMoss

Interesting challenge but there is a fairly straight forward solution.

Scaffold your data using an append, summerize and join, this will identify combinations where you have counts, and those where you don't, in which case you create a count of 0.

Next, before you cross-tab, concat the status and the act fields together and then cross-tab as you have done.

Example solution attached.

Ben

FieldNames.yxmd