/
valsight.ai

Filtering & data shaping

Use this category when you need to restrict results to a subset, select representative values per group, fill missing combinations, or create helper tables such as ranks and enumerations.

These functions help you shape the result set without changing overall dimensionality.

Start here if…

  • You want to filter a node to specific level values, for example EMEA or APAC only
  • You want to pick one value per group
  • You want to fill missing level values or combinations so calculations don’t break
  • You need ranking / enumeration helpers for further logic

Not here if…

Mental model

  • Filter functions keep only rows that match a level value or condition
  • Pick functions select one value per group
  • Fill functions add missing level values or combinations
  • Rank functions generate helper indices based on value order or dimension order
  • Map functions translate values through a lookup or threshold table

Common patterns

Filter to a list of level values

Use when you want a subset based on explicit level values.

FILTER('Node', "Level", ["A", "B"])

Filter by comparing level values

Use when the filter depends on comparing one level value to another.

LEVELFILTER('Node', "Level1", "Level2")

Pick the first value per group

Use when you need a single representative value per group.

FINDFIRST('Node')

Fill missing level values

Use when you want all level values of a dimension to be present.

FILLMISSING('Node')

Carry the last value forward

Use when you want to fill gaps with the last known value.

FILLMISSING_LAST('Node')

Fill missing values after reshaping

Use when reshaping or pivoting created missing cells to fill.

FILL_NA('Node')

Flag missing combinations

Use when you want to spot mismatched combinations between two nodes.

FINDMISSING('Node1', 'Node2')

Create a rank or enumeration helper

Use when you need indices for sorting, thresholds, or rule logic.

ENUM('Node') or ENUM_LEVEL("Level")

Map values through a lookup table

Use when you need threshold-based mapping from one measure to another.

LOOKUP('LookupValue', 'Index', 'ReturnValue')

Functions in this category

FunctionDescription
FILTERFilters the node to rows where the specified level satisfies the filter operation.
LEVELFILTERFilters the node to rows where a comparison between two level values meets a specified condition.
FINDFIRSTReturns the first value per group defined by the specified level.
FILLMISSINGFills missing level values for a dimension into the node’s data.
FILLMISSING_LASTFills missing level values for a dimension with the last available value in the node.
FILL_NAFills missing (NA) values for existing level combinations after pivoting a dimension into columns.
FINDMISSINGMarks missing level values between two nodes as 1 and existing ones as 0.
ENUMNumbers rows in ascending or descending order by value magnitude.
ENUM_LEVELNumbers level values ascending or descending in the order defined in dimension management.
LOOKUPLooks up a value in an index table and returns the corresponding value from a return table using threshold (approximate) matching.

Choosing between similar functions

FILTER vs LEVELFILTER

  • Use FILTER when you filter by explicit level values or simple conditions on one level
  • Use LEVELFILTER when the filter depends on comparing level values

FILLMISSING vs FILLMISSING_LAST

  • Use FILLMISSING when you want to add missing values using a defined fill behavior
  • Use FILLMISSING_LAST when you want to fill gaps with the last available value

FILLMISSING vs FILL_NA

  • Use FILLMISSING when you want to add missing values for a dimension or level
  • Use FILL_NA when reshaping created missing cells and you want to fill only those cells

FINDFIRST vs ENUM / ENUM_LEVEL

  • Use FINDFIRST when you want to select one value per group
  • Use ENUM / ENUM_LEVEL when you want an index or ordering helper

FINDMISSING vs IS_NA

  • Use FINDMISSING when you want to compare two nodes and detect missing combinations between them
  • Use IS_NA when you want to detect undefined values in a single node

Pitfalls & troubleshooting

  • FILTER returns empty: confirm level values match exactly (spelling/case) and the referenced level is correct
  • More rows than expected after filling: confirm that you are filling the intended dimension or level
  • Unexpected missing values after reshaping: use FILL_NA or a diagnostic helper node to inspect missing intersections
  • Single-node missingness checks: use IS_NA when you need a diagnostic flag for undefined values
Was this page helpful?