Forum Discussion
Merge tables based on date range
Hello,
A bit stuck and confused with the table merge process. I have two tables, Date Range, which has my daily date range of when a job should be executed on a particular day, and with that I have an SLA for that day. Then I have the actual job execution time - start and end time. I would like to merge both tables based on the job name, and the range which that job executed on the day falls under. In the even that the job execution for the day is out of range or did not trigger, I should be able to indicate that in the output table. Possibly merge is not the best solution, I just need to be able to look up the Date Range table with the start time to determine if that job executed on the day, then using End time to compare if that is > than SLA time, then mark it as SLA Breach.
| Date Range | |||
| Job Name | Start Time Range | End Time Range | SLA TIME |
| Batch JOB A | 9/12/2021 23:30 | 9/13/2021 6:30 | 9/13/2021 2:30 |
| Batch JOB B | 9/12/2021 23:30 | 9/13/2021 6:30 | 9/13/2021 2:30 |
| Batch JOB C | 9/12/2021 23:30 | 9/13/2021 6:30 | 9/13/2021 2:30 |
| Batch JOB D | 9/12/2021 23:30 | 9/13/2021 6:30 | 9/13/2021 2:30 |
| Batch JOB A | 9/13/2021 23:30 | 9/14/2021 6:30 | 9/14/2021 2:30 |
| Batch JOB B | 9/13/2021 23:30 | 9/14/2021 6:30 | 9/14/2021 2:30 |
| Batch JOB C | 9/13/2021 23:30 | 9/14/2021 6:30 | 9/14/2021 2:30 |
| Batch JOB D | 9/13/2021 23:30 | 9/14/2021 6:30 | 9/14/2021 2:30 |
| Job Execution | ||
| Job Name | Start Time | End Time |
| Batch JOB A | 9/12/2021 23:59 | 9/13/2021 0:32 |
| Batch JOB B | 9/13/2021 1:25 | 9/13/2021 2:45 |
| Batch JOB C | 9/13/2021 7:30 | 9/13/2021 9:32 |
| Batch JOB D | 9/12/2021 23:47 | 9/12/2021 23:53 |
| Batch JOB A | 9/14/2021 2:31 | 9/14/2021 3:12 |
| Batch JOB B | 9/14/2021 9:30 | 9/14/2021 10:01 |
|
Desired results
| Job Name | Start Time Range | End Time Range | SLA TIME | Job Name | Start Time | End Time | SLA Breach | Job Not Triggered in Range |
| Batch JOB A | 9/12/2021 23:30 | 9/13/2021 6:30 | 9/13/2021 2:30 | Batch JOB A | 9/12/2021 23:59 | 9/13/2021 0:32 | N | |
| Batch JOB B | 9/12/2021 23:30 | 9/13/2021 6:30 | 9/13/2021 2:30 | Batch JOB B | 9/13/2021 1:25 | 9/13/2021 2:45 | N | |
| Batch JOB C | 9/12/2021 23:30 | 9/13/2021 6:30 | 9/13/2021 2:30 | Y | ||||
| Batch JOB D | 9/12/2021 23:30 | 9/13/2021 6:30 | 9/13/2021 2:30 | Batch JOB D | 9/12/2021 23:47 | 9/12/2021 23:53 | N | |
| Batch JOB A | 9/13/2021 23:30 | 9/14/2021 6:30 | 9/14/2021 2:30 | Batch JOB A | 9/14/2021 0:31 | 9/14/2021 3:12 | Y | |
| Batch JOB B | 9/13/2021 23:30 | 9/14/2021 6:30 | 9/14/2021 2:30 | Y | ||||
| Batch JOB C | 9/13/2021 23:30 | 9/14/2021 6:30 | 9/14/2021 2:30 | Y | ||||
| Batch JOB D | 9/13/2021 23:30 | 9/14/2021 6:30 | 9/14/2021 2:30 | Y |
Jobs that will not match
| Batch JOB B | 9/14/2021 9:30 | 9/14/2021 10:01 |
| Batch JOB C | 9/13/2021 7:30 | 9/13/2021 9:32 |
I am pulling data from Azure App Insight via a query
3 Replies
Hi leemsiska ,
According to your description, I did the following
1.Merge two tables
2.Add a custom column named Job Not Triggered in Range.
3.Add a conditional column named SLA Breach.
Result:
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous
Thanks for responding to the request for help. I followed your steps and did not quite resolve the issue. Partly this is due to my fault as I did not present the scenario exactly as my actual data set. In the example I shared earlier, this assumes a 1:1 match between the two tables. In actual fact, table on the left (Date Range) has more records than the execution table, as this could be due to the job missing from schedule. My data set has 376 records on left and 151 on right table
Hi leemsiska ,
I believe Merge can complete your operation. Merge also has several join types you can choose to try.
About join types, you can refer to
Choose the Right Merge Join Type in Power BI
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
