Splitting & transforming data

Hi Everyone,

I am new to using Alteryx and would be grateful for your kind guidance.

I need to change the format of data in an excel file which contains information of student societies (e.g. basketball) and student IDs. I attach Dummy data in the format I currently have the information in.

The data should look like this (ideally).

Grateful for any support and guidance.

Datasets

Tips and Tricks

Transformation

Dynamic Processing

Data Investigation

Accepted answers

atcodedog05

Hi @suds144

Here is how you can do it.

Workflow:

Hope this helps : )

Transpose.yxmd

All comments

atcodedog05

Hi @suds144

You can do this with help of transpose like below

Hope this helps : )

Transpose.yxmd

Christina_H

You need a transpose tool for this:

Then it's just a case of renaming the columns and adding the society type. Assuming you have more than just sports societies, I would link to a mapping table with Society Type and Society Name columns.

suds144

Hi, thanks so much. This is super useful. I just tried it and have some questions.

How can I get the data categories to stay together? For example I want all the students who were/are part of the basketball society to be lumped together.

Some of the same students took part in different societies and there was an unequal number of students in some of the societies. For example:

I'm assuming Alteryx won't remove the duplicates? In running the proposed solution blanks appeared in the output file. Does this have something to do with the unequal number of students in each society?

Ideally I would like the output to look like this

Thank you!

suds144

Thank you very much! You're pure magic!

Gosh, is it ok to ask another question? Is there a way to map Student IDs I get from my CRM database to a list of student societies? So I know which ID corresponds to which society. I think I have to use the join tool.

Input:

Ideal output:

suds144

I created this workflow and I think it proved useful

The second input file is a list of student IDs mapped to a particular society so when I join the data together it helps me find the correct society with the standalone IDs.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703