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

Split Entries in a column into multiple Columns and Give count of each in separate output

ahsansalik
8 - Asteroid

Hi I have a very confusing task i have a column with Employee IDs concatenated in one column in an excel sheet as shown below i want them to be separated and placed in multiple columns highlighted as Bold with one Employee ID in each column. Also i want a separate output which gives me Employee ID and count of each instance of that employee ID 

 

Emp Id ConcatenatedEmp IDEMP ID
EMP100069739500,EMP000097463728EMP000097463728EMP100069739500
EMP000011837790EMP000011837790 
EMP000001378978  
EMP000032097952  
EMP000048084792,EMP000034938083,EMP000007950529  
EMP000085815232,EMP000086381208  
EMP000001595167,EMP000024663824,EMP000052109561  
EMP000090248411,EMP000046375818,EMP000062958984  
EMP000029093154,EMP000029134398,EMP000028805191  
EMP000085815232,EMP000086381208  
EMP000001595167,EMP000024663824,EMP000052109561  
EMP000085831901,EMP000085996137  
EMP000036077411,EMP000003052081,EMP000042597433,EMP000089835667  
EMP000019726088,EMP000013222261,EMP000042320429,EMP000046545004  
EMP100069739500,EMP000097463728  

 

Output 2 should be calculated by counting each employee ID in  "Emp Id Concatenated" column and shown as below

Employee IDCount
EMP0000974637283
EMP1000697395002
7 REPLIES 7
DawnDuong
13 - Pulsar
13 - Pulsar

Hi @ahsansalik 

one way to do this is:

1) use Text to Column tool, set to split into rows, with comma as delimeter.

this will generate one row for each instance of ID. For example there are 3+1+1+1 = 5 ID in the first 4 rows therefore it will give you 5 output rows 

2) connect a summary tool immediately after, grouped by ID, and count number of rows per ID (note: you should use Count, not Count Distinct)

 

to turbo charge your Alteryx skills, highly recommend that you check out the interactive lessons and recorded videos!

 

dawn 

Haider
7 - Meteor

Here is a solution based on your Data in "Emp Id Concatenated" field

ahsansalik
8 - Asteroid

Haider Thanks for the Solution I was wondering can i get count for each of the splt column separately how would i summarize based on the split columns individually 

Haider
7 - Meteor

can you paste an example as in what exactly are you after based on your data? Do you want to count how many times an ID occurs in each cell/row?

 

ahsansalik
8 - Asteroid

Yes i want count of how many times an Individual  ID has appeared in the Data. 

 

Input Output 
Emp Id Concatenated   
EMP100069739500,EMP000097463728 EMP1000697395001
EMP000029093154,EMP000029134398,EMP000028805191Output Should Look LikeEMP0000974637281
EMP000029093154,EMP000029134398,EMP000028805191 EMP0000290931543
EMP000029093154,EMP000029134398,EMP000028805191 EMP0000290931543
  EMP0000288051913
Haider
7 - Meteor

Ok here is the revised version contains both your original and new request. I think it does what you have mentioned. I have revised the original version with Dawn's suggestion - splitting to rows is much elegant. Noticed duplicates in your data, row 7 and 11 - I guess you are aware of them.

ahsansalik
8 - Asteroid

Haider that is perfect mate what i wanted thanks a lot

Labels