Recognition, normalisation and formatting of numbers and calendar values.
Purpose
Defines separate rules for fields containing numbers or dates. It can clean up textual notation, extract a number/date from surrounding text, convert it to a value suitable for calculations, or format it as report text.
Inputs and outputs
One in input and out output. Numeric rules change the selected fields; date rules can create date-part columns and remove the original field.
Parameters
| Parameter | Settings and values | Options and limits |
|---|
rules | An ordered list of rules. Rule conditions and parameters are described below. | Count: 0–256 |
Shared rule settings
| Parameter | What it configures |
|---|
field | A field, or an empty value for “All other fields”. |
replacements | Preliminary find → replace substitutions, whole for the entire value and case_sensitive for case; up to 100 substitutions. |
extract | Find a number or date among other words/characters. |
invalid | Keep with keep, clear with null, or stop with error. |
Number settings
| Parameter | What it configures |
|---|
decimal_in / thousands_in | Input decimal and thousands separators; auto or an explicit choice. |
parentheses_negative | Treat a number in parentheses as negative. |
percent | keep preserves the extracted amount; fraction converts a percentage to a fraction. |
divisor | Divide by 1, 1000 or 1000000 for units, thousands or millions. |
digits / rounding | 0–15 decimal places or no specified precision. Modes: half_up/half_even/half_down/down/up/floor/ceiling. |
sign | Keep the sign, make positive or make negative. |
output | number for calculations; text for formatted text. |
decimal / thousands / prefix / suffix | The text result’s appearance: separators, prefix and suffix. |
Date settings
| Parameter | What it configures |
|---|
order | auto, dmy, mdy or ymd for the order of input-date components. |
output / time | The result is date, text or parts; optionally retain the time. |
pattern / language | The text-date template, DD.MM.YYYY by default; name language uk/ru/en. |
parts | Parts: year, quarter, month, month_name, week, day, weekday; give each a new field name. |
parts_output | Numeric or text representation of parts. |
original | When extracting parts, keep the original field with keep, normalise it to a date with date, or remove it with remove. |
Step-by-step configuration
- Add a rule for a specific field or “All other fields”.
- Choose number or date. Add preliminary text substitutions and decide whether to search for the value within text.
- Configure recognition: number separators or date-component order.
- Choose the output: a number/date for subsequent actions or text for a report. Set the behaviour for invalid values.
- Check rows with local formats, parentheses, percentages and ambiguous dates.
Rules and diagnostics
- A rule with an empty field applies to other fields not named by separate rules.
- invalid=keep preserves an unrecognised value, null clears it, and error stops processing. Choose the policy deliberately for a controlled load.
- The text output is for presentation; keep output=number or normalise again before arithmetic.
- Percentages can be kept as the number found in the text or converted to a fraction: 15% → 0.15 with percent=fraction.
- Date parts must have unique names that do not conflict with the result.
- For output=parts, choose one specific date field and at least one part; an “All other fields” rule does not create date-part columns.
- Input decimal and thousands separators must differ; the same requirement applies to the text result.
Configuration example
For Amount, set kind=number, decimal_in=",", thousands_in=" ", extract=true, output=number, invalid=error. The text “1 234,56 UAH” becomes the number 1234.56. For DateText, choose kind=date, order=dmy, output=parts and create Year and Month.