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

[BenMoss · Accepted solution]

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

[ponraj]

Attaching a sample workflow for your case.  Hope this helps. 

i.First create a new column with act and status 

ii.Then use summarize and transpose tool to get the desired results. 

WorkflowResults

[LincolnMike]

Hi BenMoss,

thanks for the response.  I applied the recommendation to my 1.8 million records, but it seems I am getting extra data.  When I add a filter and look at the output of the formula, it looks like it added a phantom record..  Is the "count as zero" correct?

[LincolnMike]

Thanks for the response ponraj, but the output is missing the column 890_C.  I was hoping to create a column for the " "Act" even though there is not data where Status = C.  

[ponraj · Accepted solution]

Attaching the amended worklfow. Hope it gets you the desired result. 

[LincolnMike]

Hi,

sorry for the long delay.  I was pulled away to another project.  I am attempting both suggestions to my 1.7 million units, and I am seeing a few varying results.  I need some time to review the results to ensure my data is correct.

[LincolnMike]

Hi BenMoss and Ponraj,

both solutions work.  I accepted BenMoss as solution because he was first.

Can I accept 2 solutions?

Mike

[ponraj]

Yes. You can accept n number of solutions provided