Dear Celonis team.
First, let me congratulate you for continuously improving this wonderful tool.
RANGE_APPEND was a very much anticipated and need function for me. However… It misses one “small” detail that I was expecting: Gap Filling and Interpolation (GFI), like in the Vertica TIMESERIES Clause (https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AnalyzingData/TimeSeries/GapFillingAndInterpolationGFI.htm)
When showing a series of date related records, let’s say the daily account balance of my banking customers, I need to fill not just a “date value” for each day, but also to assume the same account balance of the previous day (non null record), if the balance hasn’t changed on a specific date. RANGE_APPEND does part of it. It fills the gaps in the days that don’t exist, but assumes “0” or null on the KPI values for the columns in my data table. This is OK, but not enough, regretfully.
I even tried to use the LAG function, hoping it would match each generated date value of the RANGE_APPEND, with previous non null value of my table. But it didn’t.
The Current solution
To solve my problem, which is trivial in time series analysis, I currently have a table, built in the Event Collection, using Vertica’s TIMESERIES with “Constant Interpolation”, that has one record per day, per customer, per credit account. As you can imagine, this already has 500.000.000 records just for one year of data. It’s becoming unmanageable and taking most of the contracted storage in our IBC licence, that I will need for my other data models.
Please, please, consider enhancing the RANGE_APPEND with a new parameter that would allow us to chose the type of behavior for the values in the other columns of the table: assume 0 or null (current feature), assume last valid value (constant interpolation) or even linear interpolation.