Hello,
I have data in the following format:
| EVENT_ID | Event name | 2 | 3 | 4 | Right_2 | Right_3 | Right_4 |
| 117812 | A submission has been submitted. | 5550569 | 2135102 | | Submission ID | Course Mod ID | |
| 125653 | All the submissions are being downloaded. | 2134657 | | | Course Mod ID | | |
| 125711 | An online text has been uploaded. | 5062417 | 2097811 | | Submission ID | Course Mod ID | |
| 115817 | Assignment override created | 2824 | 2120161 | 48557 | | Course Mod ID | User ID2 |
| 1192 | Comment created | 77470 | 5563610 | 2134629 | Comment ID | Submission ID | Course Mod ID |
| 105292 | Comment deleted | 79116 | 5590831 | 2120161 | Comment ID | Submission ID | Course Mod ID |
| 94204 | Course module viewed | 2134660 | | | Course Mod ID | | |
| 1372 | Discussion created | 797416 | 1827790 | | Discussion ID | Course Mod ID | |
| 120036 | Submission updated. | 1 | 2102901 | | | Course Mod ID | |
In the columns 2,3,4 are IDs which can be mapped to Right_2, Right_3, Right_4. In the last row there is an outlier number in column 2, which is not an ID. This is due to the extraction method I used before. I want to create columns Headers for the IDs they belong to and arrange the columns. I also want to delete the Outlier numbers which are not mapped to any column name.
This is the Output I am trying to achieve:
| EVENT_ID | Event name | Course Mod ID | Comment ID | Submission ID | Discussion ID | User ID2 |
| 117812 | A submission has been submitted. | 2135102 | | 5550569 | | |
| 125653 | All the submissions are being downloaded. | 2134657 | | | | |
| 125711 | An online text has been uploaded. | 2097811 | | 5062417 | | |
| 115817 | Assignment override created | 2120161 | | | | 48557 |
| 1192 | Comment created | 2134629 | 77470 | 5563610 | | |
| 105292 | Comment deleted | 2120161 | 79116 | 5590831 | | |
| 94204 | Course module viewed | 2134660 | | | | |
| 1372 | Discussion created | 1827790 | | | 797416 | |
| 120036 | Submission updated. | 2102901 | | | | |
Is there a way to do without filtering for each event and unify it afterwards?
I have attached a file, which contains all my unique event names.
Help would be much appreciated! Thank you in advance.
Best regards,
