FINDMISSING

In Filtering & data shaping

The FINDMISSING function compares two nodes and marks missing level combinations as 1 and existing ones as 0. Dependent on the Behavior input, the function finds missing level values in either or both of the nodes.

Use this function when you need to compare two nodes and identify which level combinations are missing in one or both.

Parameters

Node 1Node referenceRequired

First input node.

Node 2Node referenceRequired

Second input node.

BehaviorKeywordOptional

Indicates the leading node which is checked for missing values. Default: "FIRST".

• "FIRST": The first node is checked for missing values. This is the default.
• "SECOND": The second node is checked for missing values.
• "BOTH": Checks the combined data of Node 1 and Node 2 for missing values.

Watch out

Examples

Input node: 'Node A'

Input node: 'Node B'

Input node: 'Node C'

Checking the first node for missing level values (default)

This example keeps Node A’s rows and flags the product level values that are missing from Node B. Droplet is in Node A but not in Node B, so it is flagged with 1.

Formula: FINDMISSING('Node A', 'Node B')

Equivalent to: FINDMISSING('Node A', 'Node B', "FIRST")

Checking the second node for missing level values

This example keeps Node B’s rows and flags the product level values that are missing from Node A. Gamma is in Node B but not in Node A, so it is flagged with 1.

Formula: FINDMISSING('Node A', 'Node B', "SECOND")

Checking both nodes for missing combinations

With BOTH, the function returns every level combination that is present in either node and flags those missing from the other. It is the union of the two inputs’ rows, not a cross product, so a combination that exists in neither node (2026 and Gamma below) does not appear at all.

Formula: FINDMISSING('Node A', 'Node C', "BOTH")

Related

IS_NA

When you need to detect undefined values in a single node rather than comparing two nodes.