I have a large table with 100 different neighborhoods, each with data by every month for years 2009-2024 current. There are often months when no sales for various neighborhoods occur, which creates gaps in months, which I fill with a “Groupby/Dictionary/Cross Join” process.
Then I am using processes like linear interpolation, mean or mode to fill in the data in the gap months.
See “close price column”. This works great as long as the beginning and ending data are taken from within the exact neighborhood. But when there are gap dates at the beginning or end of the neighborhood data, and the Missing Value calculation jumps into another neighborhood of substantially different values, the imputed data is terribly skewed.
So, somehow I need to automatically group all data for each single neighborhood, one-at-a-time, run my series of Missing Value inputs, then accumulate all 100 neighborhoods back into a single data table after each has processed separately.
I’m attaching just the tiny portion of my workflow related to this process and a small sample of the total data table in Excel format.
SampleOfData.xlsx (106.0 KB)
This Excel data picks up where the Duplicate Row Filter in the process has already occurred in the workflow. You don’t need to worry about the previous steps. Just plug in the data at the first Rule Engine.
Perhaps you can guide me on how to divide the dataset into each single neighborhood (all months 2009-2024 current), allow the processes and Missing Value adjustments to occur, and how to store the accumulating neighborhood outputs to be brought back together–likely via Concatenate. Thank You!
temp.knwf (38.5 KB)