Hi Everyone, I have a following problem..
Consider 2 tables
Course Master (about 1100 rows) - Only one column as Lecture Code (Contains unique values)
| Lecture Codes |
| NIM1L4A |
| NIM1L4H |
| NIM2L4A |
| NIM2L4F |
| NIM2L4F_1A |
| NIM2L4F_1B |
| NIM2L4F_1C |
| NIM2L4G |
| NIM3L1A |
| NIM3L4A |
| NIM4L4H |
Transaction Table (about 200k rows) -
- Start and End Date & Time are pretty straightforward.
- The Keypress column contains the lecture codes a user accessed from the course. Along with other junk characters
| Start Time | End Time | Keypress |
| 29-06-2019 18.45 | 29-06-2019 18.54 | {"data":"Sub-module:2$prompt:Outro$Sub-module:3$prompt:Outro$Sub-module:4$prompt:Intro,NIM1L4A,NIM1L4B-wrong,NIM1L4C-wrong,NIM1L4D-wrong,NIM1L4E-wrong,NIM1L4F-wrong,NIM1L4G,NIM1L4H,Outro$Module:2$Sub-module:1$prompt:Intro"} |
| 29-06-2019 18.53 | 29-06-2019 18.54 | {"data":"Module:2$Sub-module:1$prompt:Outro$Sub-module:2$prompt:Outro$Sub-module:3$prompt:Outro$Sub-module:4$prompt:Intro,NIM2L4A,NIM2L4B-wrong,NIM2L4C-right,NIM2L4D-wrong,NIM2L4E-wrong,NIM2L4F-wrong,NIM2L4G,NIM2L4H,Outro$Module:1$Sub-module:1$prompt:Outro$Sub-module:2$prompt:Outro$Sub-module:3$prompt:Outro$Sub-module:4$prompt:Intro,NIM1L4A,NIM1L4B-wrong,NIM1L4C-wrong,NIM1L4D-wrong,NIM1L4E-wrong,NIM1L4F-wrong,NIM1L4G,Outro$Module:3$Sub-module:1$prompt:NIM3L1A"} |
| 29-06-2019 22.09 | 29-06-2019 22.09 | {"data":"Sub-module:1$prompt:Outro$Sub-module:2$prompt:Outro$Sub-module:3$prompt:Outro$Sub-module:4$prompt:Intro,NIM3L4A,NIM3L4B-wrong,NIM3L4C-wrong,NIM3L4D-wrong,NIM3L4E-wrong,NIM3L4F-wrong,NIM3L4G,NIM3L4H,Outro$Module:4$Sub-module:1$prompt:Outro$Sub-module:2$prompt:Outro$Sub-module:3$prompt:Outro$Sub-module:4$prompt:Intro,NIM4L4A,NIM4L4B-wrong,NIM4L4C-right,NIM4L4D-wrong,NIM4L4E-wrong,NIM4L4F-wrong,NIM4L4G,NIM4L4H,Outro$Module:1$Live_Session:2019-07-02 10:30$"} |
The Problem - Since the Keypress column has the lecture code wrapped along with other junk charaters I add 2 columns in the transaction table by extracting the first Lecture Code and last lecture code
The output should look like this
| Start Time | End Time | Keypress | First Code | Last Code |
| 29-06-2019 18.45 | 29-06-2019 18.54 | {"data":"Sub-module:2$prompt:Outro$Sub-module:3$prompt:Outro$Sub-module:4$prompt:Intro,NIM1L4A,NIM1L4B-wrong,NIM1L4C-wrong,NIM1L4D-wrong,NIM1L4E-wrong,NIM1L4F-wrong,NIM1L4G,NIM1L4H,Outro$Module:2$Sub-module:1$prompt:Intro"} | NIM1L4A | NIM1L4H |
| 29-06-2019 18.53 | 29-06-2019 18.54 | {"data":"Module:2$Sub-module:1$prompt:Outro$Sub-module:2$prompt:Outro$Sub-module:3$prompt:Outro$Sub-module:4$prompt:Intro,NIM2L4A,NIM2L4B-wrong,NIM2L4C-right,NIM2L4D-wrong,NIM2L4E-wrong,NIM2L4F-wrong,NIM2L4G,NIM2L4H,Outro$Module:1$Sub-module:1$prompt:Outro$Sub-module:2$prompt:Outro$Sub-module:3$prompt:Outro$Sub-module:4$prompt:Intro,NIM1L4A,NIM1L4B-wrong,NIM1L4C-wrong,NIM1L4D-wrong,NIM1L4E-wrong,NIM1L4F-wrong,NIM1L4G,Outro$Module:3$Sub-module:1$prompt:NIM3L1A"} | NIM2L4A | NIM3L1A |
| 29-06-2019 22.09 | 29-06-2019 22.09 | {"data":"Sub-module:1$prompt:Outro$Sub-module:2$prompt:Outro$Sub-module:3$prompt:Outro$Sub-module:4$prompt:Intro,NIM3L4A,NIM3L4B-wrong,NIM3L4C-wrong,NIM3L4D-wrong,NIM3L4E-wrong,NIM3L4F-wrong,NIM3L4G,NIM3L4H,Outro$Module:4$Sub-module:1$prompt:Outro$Sub-module:2$prompt:Outro$Sub-module:3$prompt:Outro$Sub-module:4$prompt:Intro,NIM4L4A,NIM4L4B-wrong,NIM4L4C-right,NIM4L4D-wrong,NIM4L4E-wrong,NIM4L4F-wrong,NIM4L4G,NIM4L4H,Outro$Module:1$Live_Session:2019-07-02 10:30$"} | NIM3L4A | NIM4L4H |
You can download the data here. I have tried cartesian join and it does'nt help since the data set is quite large.
Thanks so much
