Missing Data: Automatically filling in the blanks

paola · May 28, 2014 1:58PM

When working with an incomplete dataset that contains blank cells, which should be populated based on the values of neighbouring cells above, the process of filling in the missing values can be automated in the DataManager to fully prepare the data for visualisation.

In the dataset below, missing values in the field [Ctr] should take their value from the previous day OR the last filled in date, for the same Campaign. A Sort operation block can be used for better transparency, to order [Date] and [Campaign].

Formula below will pick the last available value for each combination of Date/Campaign:

IF([Ctr]=null, 
SUBSET_LASTNONNULL([Ctr], SUBSET2([Campaign],[Rank Date],[Campaign],[Rank Date],"=","<")),
[Ctr])

It relies on the [Date] ranking:
RANK([Date],[Date],true,false, SUBSET([Campaign]))
For each campaign ranking runs from 1 to n, with the earliest date being ranked 1st.

In the case where their is no logical ranking criteria to sort inside the data set, the record number field may be useful (see below)

paola · March 11, 2015 11:26AM

In the absence of any logic that can be derived from the existing fully-populated fields (e.g. if the two cells share the same value in fields X and Y, then they should share value in field Z), if the data has been imported in the order in the source daat file (like a spreadsheet) in the correct order, such that the empty cells/missing values should take the last populated value above, the following formulas can be used:

Row ID = CURRENTROW()
# Creating unique ID for every row and preserving the natural data order

IF(
[Order number]=null,
SUBSET_LASTNONNULL([Order number], SUBSET([Row ID],[Row ID],"<")),[Order number])

# picking the last populated value in the [Order number] field, but only for the records above the current row, relying on the Row ID values.

Welcome!

Categories

Ideas Parade

Tagged