Parsing Comma Separated Values Within in a Single Column Based on a Separate Count

[brichey]

Hello all, I've been struggling with parsing some comma seperated values within columns imported to alteryx from a csv. The data pertains to a messaging site my company uses, and we want to know how many times different emojis are attached to messages within the messaging platfrom. So I have a table filled with data similar to the following row (ex) :

count                              

count	emoji	userID
5,1,2	tada,clap,sparkles	1,2,3,4,5,1,1,6

That applies to one message. Somebody must have done something good....

So what this is saying is:

• 5 people used a 'tada' and their userIDs were 1,2,3,4,5
• 1 person used a 'clap' and their userID was 1
• 2 people used a 'sparkle' and their userIDs were 1,6

How I tried to approach this project: 

• I used the 'Text to Columns' parsing tool and selected to split to rows rather than columns.
• This worked well for the 'count' and 'emojis' columns, but not the 'userID' column.
  • As I'm sure you all have figured out by now, the parsing tool created rows for each of the userIDs... not what I wanted. 

 I am wondering if there is a way/ tool in alteryx that can be used to parse the userID column so that it creates a new row with (for example) the 5 userIDs who attached a 'tada' to a particular message, rather than a different row for each listed id # in the userID column. So, ideally my final output for the above example would appear as: 

count	emoji	userID
5	tada	1,2,3,4,5
1	clap	1
2	sparkles	1,6

Thanks in advance for  the help! 

[MarqueeCrew · Accepted solution]

@brichey,

The answer to your question is: YES!

Granted that I didn't preserve the order of the input {tada, clap, sparkles}, but that is doable too (must remember the original order of the emojis).  I also included a RecordID as I assumed that you need to do this for more than just one incoming record.

I suppose that the next question you might ask is:  How do I ..... ?

This was a fun puzzle to solve.  I solved it by parsing the data just as you recommended.  I put an emojiID after the 3 emoji counts (can be any #) and put an emojiID after each emoji.  I then generated rows so that I could create the right number of rows for each emoji/count.  I then had 8 rows of data and joined them by record position with the 8 userids.  I used a summarize tool to create your desired output.

The attached workflow gives you all the details. 

I'm interested in seeing how others approach this problem.  Hopefully I didn't miss an easier solution.

Cheers,

Mark

[jdunkerley79 · Accepted solution]

For @MarqueeCrew another similar approach.

I used a transpose to join count and emoji. After which split to rows on the , then pair the emoji to the count.

Then it is similar to Mark's process generate some rows, add a record id to and join to a split set of user ID

Now if I am allowed to use my formula library (https://github.com/jdunkerley/AlteryxFormulaAddOns/releases/tag/v1.1) and the split function all becomes a lot easier:

The dynamic regex part is fun (extracts the part of UserId I need without splitting :))

Both attached as 10.0 files.

James

[brichey]

 thanks a lot! This really helped!