Line break and text to column

[Aryan1182114]

Hi team

I will do my best to explain this but in case you are unable to follow, please see the input and output files for reference.

Input file column A provides the serial number, Column B provides the strength of students in a particular class (contains data with line break) and column C provides the subject name for the respective class students in that serial number.

This data needs to be converted as follows:

The subject should be the column headers and the strength of the students should be reflected within the subject in each row i.e. for every serial number the number of rows will increase based on classes in that serial number.

Also, please let me know how to resolve the line break issue in Alteryx.

Thanks

[DavidP · Accepted solution]

Here's how I would do it. Sample workflow attached.

[grazitti_sapna · Accepted solution]

Hi @Aryan1182114 , please refer to the workflow if this is what you are looking for?

Thanks.

[Aryan1182114]

Thanks @DavidP this worked perfectly.

One more questions, when we use summarize tool, it there a way to keep the other columns as is. Like we have column 'rows' here and we grouped the value with it; however, in my actual data i have multiple columns which i want to keep. Should i use 'group by' there as well.

[DavidP · Accepted solution]

Yes, group by is one option, as long as the additional grouping doesn't change the row structure of the rest of your data.

Probably a better way is to have something like a RecordID before the summarize tool and only include the fields required in the summarize tool as well as the RecordID, then join the summarized data back to the original data with a join tool using the RecordID.