Visokio website     Downloads     Video tutorials     KnowledgeBase  
Missing Data: Automatically filling in the blanks - Visokio Forums
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:
    SUBSET_LASTNONNULL([Ctr], SUBSET2([Campaign],[Rank Date],[Campaign],[Rank Date],"=","<")),

    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)

    FillBlanks.JPG 62K
  • 1 Comment
  •     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:

    # Creating unique ID for every row and preserving the natural data order 
    [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.
    Fill Gap problem.JPG 69K


It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership