Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Desktop Discussions

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

Creating column names during transpose

LincolnMike
8 - 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  
8 REPLIES 8
BenMoss
ACE Emeritus
ACE Emeritus

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

ponraj
13 - Pulsar

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. 

 

WorkflowWorkflowResultsResults

 

 

LincolnMike
8 - 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

LincolnMike
8 - 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.  

ponraj
13 - Pulsar

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

 

workflow.PNG

LincolnMike
8 - 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.

 

LincolnMike
8 - Asteroid

Hi BenMoss and Ponraj,

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

Can I accept 2 solutions?

 

Mike

ponraj
13 - Pulsar

Yes. You can accept n number of solutions provided 

Labels