FINDMISSING
Category: Filtering & data shaping
Overview
Description | 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 when you need to compare two nodes and identify which level combinations are missing in one or both. |
Syntax |
|
Parameters |
|
Limitations |
|
Examples
Input node: Node A
| Year | Product | Volume |
|---|---|---|
| 2025 | Alpha | 10 |
| 2025 | Blade | 5 |
| 2025 | Droplet | 7 |
| 2026 | Alpha | 12 |
| 2026 | Blade | 6 |
| 2026 | Droplet | 9 |
Input node: Node B
| Product | Price |
|---|---|
| Alpha | 55 |
| Blade | 65 |
| Gamma | 80 |
Input node: Node C
| Year | Product | Volume |
|---|---|---|
| 2025 | Alpha | 10 |
| 2025 | Gamma | 5 |
Checking the first node for missing level values (default)
This example checks Node A for product level values that exist in Node B but are missing in Node A. Droplet is not in Node B, so it is flagged with 1.
Formula: FINDMISSING('Node A', 'Node B') = FINDMISSING('Node A', 'Node B', FIRST)
| Year | Product | → FINDMISSING Result |
|---|---|---|
| 2025 | Alpha | 0 |
| 2025 | Blade | 0 |
| 2025 | Droplet | 1 |
| 2026 | Alpha | 0 |
| 2026 | Blade | 0 |
| 2026 | Droplet | 1 |
Checking the second node for missing level values
This example checks Node B for product level values that exist in Node A but are missing in Node B. Gamma exists in Node B but not in Node A, so it is flagged with 1.
Formula: FINDMISSING('Node A', 'Node B', SECOND)
| Product | → FINDMISSING Result |
|---|---|
| Alpha | 0 |
| Blade | 0 |
| Gamma | 1 |
Checking both nodes for missing combinations
With BOTH, the function returns the full cross product of both nodes and flags any combination that is missing from either node.
Formula: FINDMISSING('Node A', 'Node C', BOTH)
| Year | Product | → FINDMISSING Result |
|---|---|---|
| 2025 | Alpha | 0 |
| 2025 | Blade | 1 |
| 2025 | Droplet | 1 |
| 2025 | Gamma | 1 |
| 2026 | Alpha | 1 |
| 2026 | Blade | 1 |
| 2026 | Droplet | 1 |
Related Functions
| Function | When to use instead |
|---|---|
| IS_NA | When you need to detect undefined values in a single node rather than comparing two nodes. |