In the earlier sections, we have discussed three different functions commonly used in data transformation. We will now present a use case in which all three functions will complement each other to achieve a specific task.
We know that the IntervalMatch function makes use of closed intervals already defined in a table. What happens if all we have is a start date? To illustrate this scenario, look at the following screenshot:
As you can see, the End Date field has disappeared. However, there is a way for us to guess it and assign the corresponding value, based on the start date of the immediate following record. That is, if one record starts on 1-Feb-1998 and the immediate following starts on 1-Jan-2000, it means that the first interval ended on 31-Dec-1999, right?
In order for us to calculate the end date, we need to first sort the table values so that all corresponding records are contiguous, then "peek" at the start value from the next...