Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Only keep cells with values

G_SAND
8 - Asteroid

Hi.

I'm sure someone can help me with this one. I am at a certain point in my data prep and now have a spreadsheet that looks similar to this:

HeaderABCDE
GSGW   RR
GS JP   
GS KBSO  
GS    PC
JJCL    
JJ MW   
JJBG  FL 

 

And I want it to look like this:

HeaderABCDE
GSGWJPSO RR
GS KB  PC
JJCLMW FL 
JJBG    

 

The spreadsheet in question has ~150 different headers and columns A through to Z.

 

I hope there is a simple solution.

 

Thanks

3 REPLIES 3
clant
8 - Asteroid

@G_SAND I think the best way to do this would be using the Tranpose tool, remove any blanks. Then I would add a formula with the value 1, do a running total on this and group it then use the cross tab tool to get it back.

 

I have not tried it myself yet but it should work.

 

cheers

 

chris

nick_ceneviva
11 - Bolide

The attached Workflow gets the desired output you called out in your question.  It should be flexible enough that you won't have to change too much to handle many more columns.

 

Step by Step Walk through of workflow:

 

- The Summarize tool groups by the header value and then concatenates all of your other columns.  You would need to configure this tool to make sure it is summarizing all of the additional columns (Should be the only change).

- Using the header as your key, transpose all of the concatenated fields, which will allow you to handle all of the columns at once

- using the Regex parse tool, you can tokenize all of the values into separate columns.  this will need to be configure in such a way that you have enough columns for the number of rows per header.

- Use another transpose tool, to transpose the newly created columns from the Regex tool.  This will use the header and the name field from the first transpose as the key fields.

- The summarize tool groups by the Name and header field and concatenates all of the values.  At this point we are basically back to the where we were after the first transpose tool, but all of the extra commas are removed.

- Using the text to column tools, parse the concatenated values field by the columns.  Configure the tool to be split to rows so that you get separate rows for where there are multiple values.

- Use the multi row formula tool to create an index for where there are multiple rows per header and name field.  This will make it so the cross tab tool does concatenate the values back together

- Use the cross tab tool with the grouper fields being the header and the newly created index.  The label field will be the name and the values field will be the value.  Select the concatenation aggregation, but nothing will be concatenated

- The last two tools are for some final cleanup to rename the columns and drop the unnecessary columns.  By using the dynamic rename, you won't need to rename each individual column.

 

Hope that helps!

G_SAND
8 - Asteroid

Thanks @nick_ceneviva that works a treat. And the dynamic rename will come in handy for me I'm sure.

 

Thanks @clant also for his suggestion.

Labels