2 Replies Latest reply on Apr 26, 2018 10:59 AM by Gabriel Mello

    Add information when the union return null value

    Gabriel Mello

      Hello evebody.

       

      I have connected two databases.

       

      The first one have three columns: datetime, location and value

      The second database have four columns: event start, event end, event duration, event classification

       

      Like de examples bellow.

      I have done a union of the databases by the fields "datetime" and "event start".

      Now I would like to create a table where I show the datetime, minute by minute, the location, the value and the event classification (if is exist) for that minute.

       

      Basically I would like to repeat the fields of the DATABESE1 and add the "event classification" column, of the DATABASE2.

      But I would like to repeat the event classification information during all "datetime" it was occuring - I put an example bellow.

       

      Can anybody help with this?

       

       

       

      Thank you so much.

       

       

       

         DATABASE 1

      DatetimeLocationValue
      01/04/2018 00:01location 12.434,74
      01/04/2018 00:02location 12.428,33
      01/04/2018 00:03location 12.497,97
      01/04/2018 00:04location 12.450,12
      01/04/2018 00:05location 12.491,35
      01/04/2018 00:06location 12.450,33
      01/04/2018 00:07location 12.465,50
      01/04/2018 00:08location 11.121,34
      01/04/2018 00:09location 10
      01/04/2018 00:10location 10
      01/04/2018 00:11location 10
      01/04/2018 00:12location 10
      01/04/2018 00:13location 11.207,43
      01/04/2018 00:14location 11.643,03
      01/04/2018 00:15location 11.480,88
      01/04/2018 00:16location 11.551,38
      01/04/2018 00:17location 11.585,99
      01/04/2018 00:18location 11.651,36
      01/04/2018 00:19location 11.651,36
      01/04/2018 00:20location 11.659,05
      01/04/2018 00:21location 11.647,51
      01/04/2018 00:22location 11.588,55
      01/04/2018 00:23location 11.588,55
      01/04/2018 00:24location 11.569,11
      01/04/2018 00:25location 11.599,66
      01/04/2018 00:26location 11.616,75
      01/04/2018 00:27location 11.707,75
      01/04/2018 00:28location 11.766,93
      01/04/2018 00:29location 11.827,60
      01/04/2018 00:30location 11.863,92
      01/04/2018 00:31location 11.946,59
      01/04/2018 00:32location 11.956,21
      01/04/2018 00:33location 12.046,78
      01/04/2018 00:34location 12.017,30
      01/04/2018 00:35location 12.080,11
      01/04/2018 00:36location 12.075,41
      01/04/2018 00:37location 11.651,36
      01/04/2018 00:38location 11.651,36
      01/04/2018 00:39location 11.659,05
      01/04/2018 00:40location 11.647,51
      01/04/2018 00:41location 11.588,55
      01/04/2018 00:42location 11.588,55
      01/04/2018 00:43location 11.616,75
      01/04/2018 00:44location 11.707,75
      01/04/2018 00:45location 11.766,93
      01/04/2018 00:46location 11.827,60
      01/04/2018 00:47location 11.863,92
      01/04/2018 00:48location 11.946,59
      01/04/2018 00:49location 197,3295
      01/04/2018 00:50location 12.434,74
      01/04/2018 00:51location 12.428,33
      01/04/2018 00:52location 12.497,97
      01/04/2018 00:53location 12.450,12
      01/04/2018 00:54location 12.491,35
      01/04/2018 00:55location 12.450,33
      01/04/2018 00:56location 12.465,50
      01/04/2018 00:57location 11.121,34
      01/04/2018 00:58location 10
      01/04/2018 00:59location 10
      01/04/2018 01:00location 10
      01/04/2018 01:01location 10
      01/04/2018 01:02location 11.207,43
      01/04/2018 01:03location 11.643,03
      01/04/2018 01:04location 11.480,88
      01/04/2018 01:05location 11.551,38
      01/04/2018 01:06location 11.585,99
      01/04/2018 01:07location 11.651,36
      01/04/2018 01:08location 11.651,36
      01/04/2018 01:09location 11.659,05
      01/04/2018 01:10location 11.647,51
      01/04/2018 01:11location 11.588,55
      01/04/2018 01:12location 11.588,55
      01/04/2018 01:13location 11.569,11
      01/04/2018 01:14location 11.599,66
      01/04/2018 01:15location 11.616,75
      01/04/2018 01:16location 11.707,75
      01/04/2018 01:17location 11.766,93
      01/04/2018 01:18location 11.827,60
      01/04/2018 01:19location 11.863,92
      01/04/2018 01:20location 11.946,59
      01/04/2018 01:21location 11.956,21
      01/04/2018 01:22location 12.046,78
      01/04/2018 01:23location 12.017,30
      01/04/2018 01:24location 12.080,11
      01/04/2018 01:25location 12.075,41
      01/04/2018 01:26location 11.651,36
      01/04/2018 01:27location 11.651,36
      01/04/2018 01:28location 11.659,05
      01/04/2018 01:29location 11.647,51
      01/04/2018 01:30location 11.588,55
      01/04/2018 01:31location 11.588,55
      01/04/2018 01:32location 11.616,75
      01/04/2018 01:33location 11.707,75
      01/04/2018 01:34location 11.766,93
      01/04/2018 01:35location 11.827,60
      01/04/2018 01:36location 11.863,92
      01/04/2018 01:37location 11.946,59
      01/04/2018 01:38location 197,3295
      01/04/2018 01:39location 12.434,74
      01/04/2018 01:40location 12.428,33
      01/04/2018 01:41location 12.497,97
      01/04/2018 01:42location 12.450,12
      01/04/2018 01:43location 12.491,35
      01/04/2018 01:44location 12.450,33
      01/04/2018 01:45location 12.465,50
      01/04/2018 01:46location 11.121,34
      01/04/2018 01:47location 10
      01/04/2018 01:48location 10
      01/04/2018 01:49location 10
      01/04/2018 01:50location 10
      01/04/2018 01:51location 11.207,43
      01/04/2018 01:52location 11.643,03
      01/04/2018 01:53location 11.480,88
      01/04/2018 01:54location 11.551,38
      01/04/2018 01:55location 11.585,99
      01/04/2018 01:56location 11.651,36
      01/04/2018 01:57location 11.651,36
      01/04/2018 01:58location 11.659,05
      01/04/2018 01:59location 11.647,51
      01/04/2018 02:00location 11.588,55
      01/04/2018 02:01location 11.588,55
      01/04/2018 02:02location 11.569,11
      01/04/2018 02:03location 11.599,66
      01/04/2018 02:04location 11.616,75
      01/04/2018 02:05location 11.707,75
      01/04/2018 02:06location 11.766,93
      01/04/2018 02:07location 11.827,60
      01/04/2018 02:08location 11.863,92
      01/04/2018 02:09location 11.946,59
      01/04/2018 02:10location 11.956,21
      01/04/2018 02:11location 12.046,78
      01/04/2018 02:12location 12.017,30
      01/04/2018 02:13location 12.080,11
      01/04/2018 02:14location 12.075,41
      01/04/2018 02:15location 11.651,36
      01/04/2018 02:16location 11.651,36
      01/04/2018 02:17location 11.659,05
      01/04/2018 02:18location 11.647,51
      01/04/2018 02:19location 11.588,55
      01/04/2018 02:20location 11.588,55
      01/04/2018 02:21location 11.616,75
      01/04/2018 02:22location 11.707,75
      01/04/2018 02:23location 11.766,93
      01/04/2018 02:24location 11.827,60
      01/04/2018 02:25location 11.863,92
      01/04/2018 02:26location 11.946,59
      01/04/2018 02:27location 197,3295
      01/04/2018 02:28location 12.434,74
      01/04/2018 02:29location 12.428,33
      01/04/2018 02:30location 12.497,97
      01/04/2018 02:31location 12.450,12
      01/04/2018 02:32location 12.491,35
      01/04/2018 02:33location 12.450,33
      01/04/2018 02:34location 12.465,50
      01/04/2018 02:35location 11.121,34
      01/04/2018 02:36location 10
      01/04/2018 02:37location 10
      01/04/2018 02:38location 10
      01/04/2018 02:39location 10
      01/04/2018 02:40location 11.207,43
      01/04/2018 02:41location 11.643,03
      01/04/2018 02:42location 11.480,88
      01/04/2018 02:43location 11.551,38
      01/04/2018 02:44location 11.585,99
      01/04/2018 02:45location 11.651,36
      01/04/2018 02:46location 11.651,36
      01/04/2018 02:47location 11.659,05
      01/04/2018 02:48location 11.647,51
      01/04/2018 02:49location 11.588,55
      01/04/2018 02:50location 11.588,55
      01/04/2018 02:51location 11.569,11
      01/04/2018 02:52location 11.599,66
      01/04/2018 02:53location 11.616,75
      01/04/2018 02:54location 11.707,75
      01/04/2018 02:55location 11.766,93
      01/04/2018 02:56location 11.827,60
      01/04/2018 02:57location 11.863,92
      01/04/2018 02:58location 11.946,59
      01/04/2018 02:59location 11.956,21
      01/04/2018 03:00location 12.046,78
      01/04/2018 03:01location 12.017,30
      01/04/2018 03:02location 12.080,11
      01/04/2018 03:03location 12.075,41
      01/04/2018 03:04location 11.651,36
      01/04/2018 03:05location 11.651,36
      01/04/2018 03:06location 11.659,05
      01/04/2018 03:07location 11.647,51
      01/04/2018 03:08location 11.588,55
      01/04/2018 03:09location 11.588,55
      01/04/2018 03:10location 11.616,75
      01/04/2018 03:11location 11.707,75
      01/04/2018 03:12location 11.766,93
      01/04/2018 03:13location 11.827,60
      01/04/2018 03:14location 11.863,92
      01/04/2018 03:15location 11.946,59
      01/04/2018 03:16location 197,3295
      01/04/2018 03:17location 12.434,74
      01/04/2018 03:18location 12.428,33
      01/04/2018 03:19location 12.497,97
      01/04/2018 03:20location 12.450,12
      01/04/2018 03:21location 12.491,35
      01/04/2018 03:22location 12.450,33
      01/04/2018 03:23location 12.465,50
      01/04/2018 03:24location 11.121,34
      01/04/2018 03:25location 10
      01/04/2018 03:26location 10
      01/04/2018 03:27location 10
      01/04/2018 03:28location 10
      01/04/2018 03:29location 11.207,43
      01/04/2018 03:30location 11.643,03
      01/04/2018 03:31location 11.480,88
      01/04/2018 03:32location 11.551,38
      01/04/2018 03:33location 11.585,99
      01/04/2018 03:34location 11.651,36
      01/04/2018 03:35location 11.651,36
      01/04/2018 03:36location 11.659,05
      01/04/2018 03:37location 11.647,51
      01/04/2018 03:38location 11.588,55
      01/04/2018 03:39location 11.588,55
      01/04/2018 03:40location 11.569,11
      01/04/2018 03:41location 11.599,66
      01/04/2018 03:42location 11.616,75
      01/04/2018 03:43location 11.707,75
      01/04/2018 03:44location 11.766,93
      01/04/2018 03:45location 11.827,60
      01/04/2018 03:46location 11.863,92
      01/04/2018 03:47location 11.946,59
      01/04/2018 03:48location 11.956,21
      01/04/2018 03:49location 12.046,78
      01/04/2018 03:50location 12.017,30
      01/04/2018 03:51location 12.080,11
      01/04/2018 03:52location 12.075,41
      01/04/2018 03:53location 11.651,36
      01/04/2018 03:54location 11.651,36
      01/04/2018 03:55location 11.659,05
      01/04/2018 03:56location 11.647,51
      01/04/2018 03:57location 11.588,55
      01/04/2018 03:58location 11.588,55

       

       

      DATABASE2

       

         

      Event startEvent endEvent duration (min)Event classification
      01/04/2018 00:0701/04/2018 00:2215Planned
      01/04/2018 00:2401/04/2018 00:3612Planned
      01/04/2018 03:3701/04/2018 03:5821Planned
      01/04/2018 01:3301/04/2018 02:2552Unplanned

       

       

      DASHBOARD - TABLE

         

      DatetimeLocationValueEvent Classification
      01/04/2018 00:01location 12.434,74
      01/04/2018 00:02location 12.428,33
      01/04/2018 00:03location 12.497,97
      01/04/2018 00:04location 12.450,12
      01/04/2018 00:05location 12.491,35
      01/04/2018 00:06location 12.450,33
      01/04/2018 00:07location 12.465,50Planned
      01/04/2018 00:08location 11.121,34Planned
      01/04/2018 00:09location 10Planned
      01/04/2018 00:10location 10Planned
      01/04/2018 00:11location 10Planned
      01/04/2018 00:12location 10Planned
      01/04/2018 00:13location 11.207,43Planned
      01/04/2018 00:14location 11.643,03Planned
      01/04/2018 00:15location 11.480,88Planned
      01/04/2018 00:16location 11.551,38Planned
      01/04/2018 00:17location 11.585,99Planned
      01/04/2018 00:18location 11.651,36Planned
      01/04/2018 00:19location 11.651,36Planned
      01/04/2018 00:20location 11.659,05Planned
      01/04/2018 00:21location 11.647,51Planned
      01/04/2018 00:22location 11.588,55
      01/04/2018 00:23location 11.588,55
      01/04/2018 00:24location 11.569,11Planned
      01/04/2018 00:25location 11.599,66Planned
      01/04/2018 00:26location 11.616,75Planned
      01/04/2018 00:27location 11.707,75Planned
      01/04/2018 00:28location 11.766,93Planned
      01/04/2018 00:29location 11.827,60Planned
      01/04/2018 00:30location 11.863,92Planned
      01/04/2018 00:31location 11.946,59Planned
      01/04/2018 00:32location 11.956,21Planned
      01/04/2018 00:33location 12.046,78Planned
      01/04/2018 00:34location 12.017,30Planned
      01/04/2018 00:35location 12.080,11Planned
      01/04/2018 00:36location 12.075,41
      01/04/2018 00:37location 11.651,36
      01/04/2018 00:38location 11.651,36
      01/04/2018 00:39location 11.659,05
      01/04/2018 00:40location 11.647,51
      01/04/2018 00:41location 11.588,55
      01/04/2018 00:42location 11.588,55
      01/04/2018 00:43location 11.616,75
      01/04/2018 00:44location 11.707,75
      01/04/2018 00:45location 11.766,93
      01/04/2018 00:46location 11.827,60
      01/04/2018 00:47location 11.863,92
      01/04/2018 00:48location 11.946,59
      01/04/2018 00:49location 197,3295
      01/04/2018 00:50location 12.434,74
      01/04/2018 00:51location 12.428,33
      01/04/2018 00:52location 12.497,97
      01/04/2018 00:53location 12.450,12
      01/04/2018 00:54location 12.491,35
      01/04/2018 00:55location 12.450,33
      01/04/2018 00:56location 12.465,50
      01/04/2018 00:57location 11.121,34
      01/04/2018 00:58location 10
      01/04/2018 00:59location 10
      01/04/2018 01:00location 10
      01/04/2018 01:01location 10
      01/04/2018 01:02location 11.207,43
      01/04/2018 01:03location 11.643,03
      01/04/2018 01:04location 11.480,88
      01/04/2018 01:05location 11.551,38
      01/04/2018 01:06location 11.585,99
      01/04/2018 01:07location 11.651,36
      01/04/2018 01:08location 11.651,36
      01/04/2018 01:09location 11.659,05
      01/04/2018 01:10location 11.647,51
      01/04/2018 01:11location 11.588,55
      01/04/2018 01:12location 11.588,55
      01/04/2018 01:13location 11.569,11
      01/04/2018 01:14location 11.599,66
      01/04/2018 01:15location 11.616,75
      01/04/2018 01:16location 11.707,75
      01/04/2018 01:17location 11.766,93
      01/04/2018 01:18location 11.827,60
      01/04/2018 01:19location 11.863,92
      01/04/2018 01:20location 11.946,59
      01/04/2018 01:21location 11.956,21
      01/04/2018 01:22location 12.046,78
      01/04/2018 01:23location 12.017,30
      01/04/2018 01:24location 12.080,11
      01/04/2018 01:25location 12.075,41
      01/04/2018 01:26location 11.651,36
      01/04/2018 01:27location 11.651,36
      01/04/2018 01:28location 11.659,05
      01/04/2018 01:29location 11.647,51
      01/04/2018 01:30location 11.588,55
      01/04/2018 01:31location 11.588,55
      01/04/2018 01:32location 11.616,75
      01/04/2018 01:33location 11.707,75Unplanned
      01/04/2018 01:34location 11.766,93Unplanned
      01/04/2018 01:35location 11.827,60Unplanned
      01/04/2018 01:36location 11.863,92Unplanned
      01/04/2018 01:37location 11.946,59Unplanned
      01/04/2018 01:38location 197,3295Unplanned
      01/04/2018 01:39location 12.434,74Unplanned
      01/04/2018 01:40location 12.428,33Unplanned
      01/04/2018 01:41location 12.497,97Unplanned
      01/04/2018 01:42location 12.450,12Unplanned
      01/04/2018 01:43location 12.491,35Unplanned
      01/04/2018 01:44location 12.450,33Unplanned
      01/04/2018 01:45location 12.465,50Unplanned
      01/04/2018 01:46location 11.121,34Unplanned
      01/04/2018 01:47location 10Unplanned
      01/04/2018 01:48location 10Unplanned
      01/04/2018 01:49location 10Unplanned
      01/04/2018 01:50location 10Unplanned
      01/04/2018 01:51location 11.207,43Unplanned
      01/04/2018 01:52location 11.643,03Unplanned
      01/04/2018 01:53location 11.480,88Unplanned
      01/04/2018 01:54location 11.551,38Unplanned
      01/04/2018 01:55location 11.585,99Unplanned
      01/04/2018 01:56location 11.651,36Unplanned
      01/04/2018 01:57location 11.651,36Unplanned
      01/04/2018 01:58location 11.659,05Unplanned
      01/04/2018 01:59location 11.647,51Unplanned
      01/04/2018 02:00location 11.588,55Unplanned
      01/04/2018 02:01location 11.588,55Unplanned
      01/04/2018 02:02location 11.569,11Unplanned
      01/04/2018 02:03location 11.599,66Unplanned
      01/04/2018 02:04location 11.616,75Unplanned
      01/04/2018 02:05location 11.707,75Unplanned
      01/04/2018 02:06location 11.766,93Unplanned
      01/04/2018 02:07location 11.827,60Unplanned
      01/04/2018 02:08location 11.863,92Unplanned
      01/04/2018 02:09location 11.946,59Unplanned
      01/04/2018 02:10location 11.956,21Unplanned
      01/04/2018 02:11location 12.046,78Unplanned
      01/04/2018 02:12location 12.017,30Unplanned
      01/04/2018 02:13location 12.080,11Unplanned
      01/04/2018 02:14location 12.075,41Unplanned
      01/04/2018 02:15location 11.651,36Unplanned
      01/04/2018 02:16location 11.651,36Unplanned
      01/04/2018 02:17location 11.659,05Unplanned
      01/04/2018 02:18location 11.647,51Unplanned
      01/04/2018 02:19location 11.588,55Unplanned
      01/04/2018 02:20location 11.588,55Unplanned
      01/04/2018 02:21location 11.616,75Unplanned
      01/04/2018 02:22location 11.707,75Unplanned
      01/04/2018 02:23location 11.766,93Unplanned
      01/04/2018 02:24location 11.827,60Unplanned
      01/04/2018 02:25location 11.863,92Unplanned
      01/04/2018 02:26location 11.946,59
      01/04/2018 02:27location 197,3295
      01/04/2018 02:28location 12.434,74
      01/04/2018 02:29location 12.428,33
      01/04/2018 02:30location 12.497,97
      01/04/2018 02:31location 12.450,12
      01/04/2018 02:32location 12.491,35
      01/04/2018 02:33location 12.450,33
      01/04/2018 02:34location 12.465,50
      01/04/2018 02:35location 11.121,34
      01/04/2018 02:36location 10
      01/04/2018 02:37location 10
      01/04/2018 02:38location 10
      01/04/2018 02:39location 10
      01/04/2018 02:40location 11.207,43
      01/04/2018 02:41location 11.643,03
      01/04/2018 02:42location 11.480,88
      01/04/2018 02:43location 11.551,38
      01/04/2018 02:44location 11.585,99
      01/04/2018 02:45location 11.651,36
      01/04/2018 02:46location 11.651,36
      01/04/2018 02:47location 11.659,05
      01/04/2018 02:48location 11.647,51
      01/04/2018 02:49location 11.588,55
      01/04/2018 02:50location 11.588,55
      01/04/2018 02:51location 11.569,11
      01/04/2018 02:52location 11.599,66
      01/04/2018 02:53location 11.616,75
      01/04/2018 02:54location 11.707,75
      01/04/2018 02:55location 11.766,93
      01/04/2018 02:56location 11.827,60
      01/04/2018 02:57location 11.863,92
      01/04/2018 02:58location 11.946,59
      01/04/2018 02:59location 11.956,21
      01/04/2018 03:00location 12.046,78
      01/04/2018 03:01location 12.017,30
      01/04/2018 03:02location 12.080,11
      01/04/2018 03:03location 12.075,41
      01/04/2018 03:04location 11.651,36
      01/04/2018 03:05location 11.651,36
      01/04/2018 03:06location 11.659,05
      01/04/2018 03:07location 11.647,51
      01/04/2018 03:08location 11.588,55
      01/04/2018 03:09location 11.588,55
      01/04/2018 03:10location 11.616,75
      01/04/2018 03:11location 11.707,75
      01/04/2018 03:12location 11.766,93
      01/04/2018 03:13location 11.827,60
      01/04/2018 03:14location 11.863,92
      01/04/2018 03:15location 11.946,59
      01/04/2018 03:16location 197,3295
      01/04/2018 03:17location 12.434,74
      01/04/2018 03:18location 12.428,33
      01/04/2018 03:19location 12.497,97
      01/04/2018 03:20location 12.450,12
      01/04/2018 03:21location 12.491,35
      01/04/2018 03:22location 12.450,33
      01/04/2018 03:23location 12.465,50
      01/04/2018 03:24location 11.121,34
      01/04/2018 03:25location 10
      01/04/2018 03:26location 10
      01/04/2018 03:27location 10
      01/04/2018 03:28location 10
      01/04/2018 03:29location 11.207,43
      01/04/2018 03:30location 11.643,03
      01/04/2018 03:31location 11.480,88
      01/04/2018 03:32location 11.551,38
      01/04/2018 03:33location 11.585,99
      01/04/2018 03:34location 11.651,36
      01/04/2018 03:35location 11.651,36
      01/04/2018 03:36location 11.659,05
      01/04/2018 03:37location 11.647,51Planned
      01/04/2018 03:38location 11.588,55Planned
      01/04/2018 03:39location 11.588,55Planned
      01/04/2018 03:40location 11.569,11Planned
      01/04/2018 03:41location 11.599,66Planned
      01/04/2018 03:42location 11.616,75Planned
      01/04/2018 03:43location 11.707,75Planned
      01/04/2018 03:44location 11.766,93Planned
      01/04/2018 03:45location 11.827,60Planned
      01/04/2018 03:46location 11.863,92Planned
      01/04/2018 03:47location 11.946,59Planned
      01/04/2018 03:48location 11.956,21Planned
      01/04/2018 03:49location 12.046,78Planned
      01/04/2018 03:50location 12.017,30Planned
      01/04/2018 03:51location 12.080,11Planned
      01/04/2018 03:52location 12.075,41Planned
      01/04/2018 03:53location 11.651,36Planned
      01/04/2018 03:54location 11.651,36Planned
      01/04/2018 03:55location 11.659,05Planned
      01/04/2018 03:56location 11.647,51Planned
      01/04/2018 03:57location 11.588,55Planned
      01/04/2018 03:58location 11.588,55Planned