Data Validation: Adding Leading Zeros

aa1708 — Wed, 23 Nov 2016 10:45:42 -0500

Hi there, I have a column with hundreds of phone numbers but some records don't have leading zeros. Is there a way to construct a formulae to add zeros to the ones without?

Missing Data: Automatically filling in the blanks

paola — Wed, 28 May 2014 13:58:50 -0400

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)

Tagged with missing_data - Visokio Forums

Data Validation: Adding Leading Zeros

Missing Data: Automatically filling in the blanks