Hi - I have a dataset with the following structure: ID NUMBERMODULEMARKEXAM DATE123ABC70%10/01/2020123DEF80%11/01/2020124GHI50%13/01/2020124JKL55%12/01/2020 But need it to look like this for a statutory return: ID NUMBERMODULEMARKEXAM DATEMODULEMARKEXAM DATE123ABC70%10/01/2020DEF80%11/01/2020124GHI50%13/01/2020JKL55%12/01/2020 Does anyone have any ideas how to achieve this as I've not been able to find any comparable examples in the community. Many thanks, Alex

Creating repeating columns from a normalised DB

Hi - I have a dataset with the following structure:

ID NUMBER	MODULE	MARK	EXAM DATE
123	ABC	70%	10/01/2020
123	DEF	80%	11/01/2020
124	GHI	50%	13/01/2020
124	JKL	55%	12/01/2020

But need it to look like this for a statutory return:

ID NUMBER	MODULE	MARK	EXAM DATE	MODULE	MARK	EXAM DATE
123	ABC	70%	10/01/2020	DEF	80%	11/01/2020
124	GHI	50%	13/01/2020	JKL	55%	12/01/2020

Does anyone have any ideas how to achieve this as I've not been able to find any comparable examples in the community.

Many thanks,

Alex

Transformation

Accepted answers

Emil_Kos

Hi @alexrossiter,

Actually, I was able to finish this exercise.

I learned something new with this one. Thanks for that 😄

If this works for you please mark it as a solution!

Make column.yxmd

alexrossiter

Brilliant Thanks, Alex.

All comments

Emil_Kos

Hi @alexrossiter,

I have crerated a workflow for you:

The only downside is that I needed to cheat a little bit as you can't have the same column name twice in the data. So one of the columns is called "Mark" and the second one is "Mark " etc.

I hope it will work for you!

join2.yxmd

alexrossiter

Thanks Emil - that works great when a student has two modules. Have you any idea how it could be extended if they are doing more - ideally so the columns continue to repeat until all modules for that ID have been placed on the same line.

Thanks,

Alex

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703