community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Creating column names during transpose

Asteroid

Hi, 

I have the following data and attached a Workflow.

ActunitStatus
123abcC
567abcO
123defO
567ghiC
567jklC
789mnoC
890mnoO
789pqrO

 

I use Summary and crosstab tools for this display:

Statusunit123567789890
Cabc1   
Oabc 1  
Odef1   
Cghi 1  
Cjkl 1  
Cmno  1 
Omno   1
Opqr  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?

Unit123_C123_O567_C567_O789_C789_O890_C890_O
abc1  1    
def 1      
ghi  1     
jkl  1     
mno    1  1
pqr     1  
Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

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.

 

2018-06-15_22-25-20.png

 

Example solution attached.

 

Ben

Quasar

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. 

 

workflow.PNGWorkflowResults.PNGResults

 

 

Asteroid

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?result.JPG

Asteroid

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.  

Quasar

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

 

workflow.PNG

Asteroid

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.

 

Asteroid

Hi BenMoss and Ponraj,

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

Can I accept 2 solutions?

 

Mike

Quasar

Yes. You can accept n number of solutions provided 

Labels