How to Rename Dynamic Year-Based Columns and Pass Column Lists as Parameters in Sparkflows?

[admin]

Question

The column name changes every year. For example:

Sum of Q1'25, Sum of Q2'25, Sum of Q3'25, Sum of Q4'25, Sum of FY'25

For year 2026, it becomes:

Sum of Q1'26

How do we read and rename the columns to a fixed value?

Answer

In DynamicRename:

• Replace Pattern: '\d{2}$
• With Pattern Replacement: ${YEAR}
  where YEAR is parameterized.
• Enable the option to check all columns.

In Parameter:

AfterReplace:

Also, use Fire macros to retrieve the dynamic year value at runtime:

Macros in Workflows — Sparkflows 3.0 documentation

The column name is dynamic, and we need to impute the null value with 0.0.

Below are two options:

With BulkColumnExpression

We currently use a generic Excel Reader workflow that has parameters so we can reuse the workflow in many areas. However, we have the requirement of selecting a specific list of columns and ignoring the other columns.

We are using the Select tool so the output can be stable. Can we pass the list of columns to the workflow instead of hardcoding the list of columns in the workflow?

Use the SelectColumns node, and the Columns field can be parameterized like below.

In the above, the parameter name is:

workflow_6_columns

Where:

• 6 is the node_id
• columns is the fieldName

It takes comma-separated column names as the parameter value.

Example while passing from the command line:

workflow_6_columns=“id,column1,column2”