Line break and text to column

Question

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

Input.xlsx

Output.xlsx

DavidP · Accepted Answer

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

grazitti_sapna · Accepted Answer

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

Thanks.

DavidP · Accepted Answer

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.