LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: mmarinelli

When loading any data source, a problem-agnostic test which should be applied if possible is to determine if the source is complete, meaning that there are no gaps in the data set. If the set has some sort of implicit unique record identifier, such as a row ID, what node can be used to search for gaps in the sequence of identifiers?

The attached graph presents an example of this approach. Typically, a Filter node is used to apply some BRAINScript once per row of input data, but logic is not applied across the row set. In this case, we need to compare each row to the preceeding one (this example requires a sorted data set) , so we maintain a cursor variable (lastRecordLoaded) which is consulted as each row is visited to determine the value of RECORDID for the last row. If the current value is 1 greater than the last, we output the record and move on, but if not, we produce a count of the missing records (current RECORDID - last RECORDID) and we output a record on the second pin.

The BRAINScript from this Filter node follows:
----
RECORDIDEXPR = {{^RecordIDExpr^}}

# need to initialize this variable to something before we can use it
if firstExec then
lastRecordLoaded = null

# check the last Record ID (set after each row is read) against the current Record ID
if RECORDIDEXPR == lastRecordLoaded + 1 or lastRecordLoaded.isNull() then
{
# all ok
inSeq = true
missingCount = 0
}
else
{
# out of sequence, set variable to false and count the gap between this and the last Record found
inSeq = false
missingCount = RECORDIDEXPR - lastRecordLoaded - 1
}

# set the last Record ID, which will be used for comparison upon the next row reading
lastRecordLoaded = RECORDIDEXPR

output 1
{
emit *, inSeq, missingCount
}

output 2
{
emit RECORDIDEXPR, inSeq, missingCount
where not inSeq
}
----

A more sophisticated version of this node, which allowed users to specify the iteration step (e.g. 10 instead of 1) or which handled other data types (e.g. dates) could be created using this technique.
Attachments:
sequence_example.brg