Split column into different outputs

[JoaoFernandes]

Hi friends,

I have to do a basic task on Alteryx but I'm having doubts about it. I have a file that looks like this:

Student ID	Score
SSS1	12
SSS1	14
SSS1	16
SSS1	13
SSS2	14
SSS2	14
SSS3	15
SSS3	20
SSS3	11

My intetion is to split this table into different outputs (without exporting) everytime the "student ID" changes. So for every "student ID", I want to create a table. The output should look like this:

Table 1

Student ID	Score
SSS1	12
SSS1	14
SSS1	16
SSS1	13

Table 2

Student ID	Score
SSS2	14
SSS2	14

Table 3

Student ID	Score
SSS3	15
SSS3	20
SSS3	11

Etc.

Unfortunately I can't use the filter tool because I never know exactly which ID's I have, nor how many. Does anybody know how to? Thank you very much.

[AmeliaG]

Hi @JoaoFernandes,

Thanks for your question! Within the output data tool, there is an option to 'Take file or table name from field'. In this instance, you should select that option and the field you would use would be the 'Student ID' field. This knowledge base article details how to set it up. 

The output will change depending on your file type. If you are using Excel, it will create a separate sheet for each student. If using CSV, it would create a separate file for each student. 

Let me know if you have any additional questions and I'd be happy to help!

Amelia

[JoaoFernandes]

Hi Amelia G,

thanks for your answer! Unfortunately I know about that option but I can't use it since I don't want to export the data right away, I just want to produce an individual table per "Student ID". Do you know if I should use a Multi Row Formula?

Thanks again for your help!

[AmeliaG]

Hi @JoaoFernandes,

It sounds like you need to either:

1. Use the 'Group By' function in a given tool, to group by the student id. 

2. Create a batch macro where your 'Control Parameter' is the student id. This is essentially a 'Group By' for the entire process within the macro. Meaning, take the records for each student id separately and run them through this process. Here is a great training video to get you started with batch macros. 

In Alteryx you don't need to actually create separate tables in order for the data to be treated like separate tables in the process. The only time you would actually break out the data is upon output. 

What are you trying to do with your data after it is grouped?

[JoaoFernandes]

HI AmeliaG

I'm trying to divide into different tables because I want to create a unique ID for each student. If I use the Record ID tool, I will get something like this:

Student ID	Score	Record ID
SSS1	12	1
SSS1	14	2
SSS1	16	3
SSS1	13	4
SSS2	14	5
SSS2	14	6
SSS3	15	7
SSS3	20	8
SSS3	11	9

But I would like something like this:

Student ID	Score	Record ID
SSS1	12	1
SSS1	14	2
SSS1	16	3
SSS1	13	4
SSS2	14	1
SSS2	14	2
SSS3	15	1
SSS3	20	2
SSS3	11	3

So I figured the best way to do this is by spliting into different tables. I will try the macro solution, thanks!

[AmeliaG · Accepted solution]

Hi @JoaoFernandes,

Thanks for that explanation! There are two ways you can achieve your desired results:

1. Using the 'Multi-Row Formula' tool and leveraging the 'Group By' function to 'Group By' Student ID

2. Use the Tile tool and tile on 'Unique Value'

I've attached a workflow which show both methods.  

Have a great day!

Amelia

[JoaoFernandes]

Those workflows work great! Thanks AmeliaG!