How can I fill an empty value with the one from the previous (or next) column?

[4581b17319aaf33d309d]

Hi everyone,

I have daily data used in time series. For some reasons, sometimes a piece of data is missing. To avoid a gap in my time series, I would like to automatically detect those missing values and fill them with the previous value (or even better, with the average between the previous and the next values if it's even possible).

I know the FILL fonction, but it only works with values from the same column. Here I want values from the preceding and/or following columns (see example attached).

Thanks for your help.

[Trifacta_Alumni]

Hi Mickael,

Here is a work-around approach that you can try:

1. Un-pivot
2. Fill as normal
3. Pivot

In the following, the Bar column started out with an empty value. I filled it with the average of Foo and Baz:

I have attached this super-minimal but working example as a zip archive containing the data and recipe (tested in free Cloud Wrangler). As you can see, much of the recipe is "housekeeping". Your mileage may vary, but I encourage you to play around with this approach. Hope it helps!

In the meantime, I have passed on your use case and enhancement request to the product development team.

Cheers,

Nathanael

[4581b17319aaf33d309d]

Thank you for your help Nathanael.

In my case, filling with the average value is not suitable. So I tried filling with the last valid value but I got a "Cannot read property 'value' of undefined" error. Any idea?

Thanks

MM

[Trifacta_Alumni · Accepted solution]

Hi Mickael,

It's difficult to say without seeing your recipe and sample data. But, when I modified my toy recipe above to use fill down instead of average, I too encountered a problem. In my case, the problem was with how the values should be sorted (which is a requirement of all window transforms). Selecting 'Default' resulted in an invalid step, as did selecting 'Column' and then providing the function 'SOURCEROWNUMBER()' as the argument. Then I remembered that certain operations such as pivoting and un-pivoting nullify any reference to the original source row number. The workaround is to manually introduce a column using the SOURCEROWNUMBER() function prior to these operations. This is what I have done in the updated recipe, attached. Is this relevant for you?

Cheers,

Nathanael

[Trifacta_Alumni]

Apologies, I meant to include a screenshot of the result. In the original recipe, the Bar column contained 2 (i.e., the average of 1 and 3). As you can see, it now contains 1 (the fill-down of the last valid value from Foo):

[4581b17319aaf33d309d]

Thanks Nathanael,

I was struggling with excactly what you described! This workaround solved my issue. Thanks a lot.

I hope this enhancement request will be approved, though ;-)

Have a nice day.

Mickael

[Trifacta_Alumni]

Glad it worked out!

No promises, but the product team agreed that it would be a good addition. :)

Cheers,

Nathanael