The normalise statement is used to update the values in a numerical column such that they are all positive, negative or inverted.
In this documentation the spelling normalise is used but normalize may also be used. The functionality is identical in either case.
normalise columncolNameas positive
normalise columncolNameas negative
normalise columncolNameas invert
normalise columncolNameas standard
The normalise statement processes each value in the column called colName and applies the following logic based on the last argument shown above as follows:
All negative numbers are replaced with their positive equivalent. Non-negative numbers are left unmodified.
All positive numbers are replaced with their negative equivalent. Negative numbers are left unmodified.
All positive numbers are replaced with their negative equivalent, and all negative numbers are replaced with their positive equivalent
All non-blank values are assumed to be a decimal number and are replaced with that value in conventional notation. This functionality is intended to provide a means to convert numbers in scientific notation such as 2.1E-5 to conventional notation such as 0.000021.
In order to be considered a number, a value in the colName column must start with any of the characters +, -, . or 0 to 9 and may contain a single . character which is interpreted as a decimal point.
If a value in colName is non-numeric or blank it is left intact
When using standardall non-blank values are assumed to be numeric, and as such any non-numeric values will be changed to a numeric zero.
Any numerical value in colName which starts with a +, . or decimal character is considered positive
Any numerical value in colName which starts with a - character is considered negative
When using standard the resulting conventional number will be accurate up to 14 decimal places
The normalise statement ignores the option overwrite setting, as its sole purpose is to modify existing values.
import "system/extracted/csp_usage.csv" source test alias data
# Invert all numerical values in column 'quantity'