Time Series imputation is required for the machine learning and reporting. How do you do this in Alteryx?
Time Series Imputation in Alteryx
For example, thinking about the daily data. Looking at the data row by row, if all the date difference between each record is 1 day, then there are no lack. In this case, you can use the Multi-Row Formula tool to do this task.
However, do you know the TS Filler tool in the Time Series category?
This tool allows you to fill the consecutive time series data easily. Note that it is needed R installer.
How to use the TS Filler tool
Setting up the TS Filler tool is very easy. Select a specific field which you want to complement, the interval(Day unit or Month unit and so on) and increment value.
Supposed that there is the data bellow. They are months between Jan 2024 and Jan 2025, but there are no September, November and December.
Let’s set it to TS Filler tool with interval set to months and increment set to 1.
As above screenshot, your specified field is filled and the original field is renamed as “OriginalDateTime”. Ans also, the filled records are set to “True” on the FlagGeneratedRow field. However, the existing fields values at the all filled records are null.
An important point to note
It is not possible to set the interval to seconds. If you want to complement the values by second unit, please use the Multi-Row Formula tool. For example, the workflow complementing by day unit is as follows. Please see the sample workflow when you want to see the detail.
However, complementing by second unit is that the data can grow significantly. For example, the lack of record is for one day, it will make 86,400 records by second unit. Please consider the necessity and affect before do it!
If the increment of original data is 1 and the increment value is set to 2, what’s happened?
For example, there are each month between Jan 2024 and Feb 2025 by month unit, but the May, August and December are not existed, which is as follows.
For this data, if you set the increment to 2 and interval to “Month”, what’s happened? The setting as follows.
The result is as follows.
Fabruary is replaced by March and there are multiple March. And April is replaced by May which is not existed before processing. The ideal result is January, March, May …. unfortunatelly the same months have been generated. To complement correctly, you can use the Unique Tool.
Conclusion
- I have explained how to complement the time series data by Alteryx. It is easy to use the TS Filler tool for it.
- If the unit is second, it should be done by Multi-Row Formula tool.
- If the data is in units smaller than the increment setting, please remove duplicates using the Unique tool.
Sample Workflow Download
The next post is …
The next blog is about Reporting category tool, which is very standard tips.
コメント