Transform
The Transform() function takes an input range, performs one or more calculations on that data, and returns the specified columns.
Syntax:
Qx.Transform(Range, Formulas, [Calculate Error Value])- In Excel with Quintessence add-in versions prior to 25.1, use:
Transform() - In the Quintessence Editor (Studio) using Quintessence language, use:
Transform() - In the Quintessence Editor (Studio) using Python language, use:
Qx.Transform()
ⓘ See parameter descriptions
| Range | The input range. |
| Formulas | The formulas or calculations to apply to the input. Within formulas, columns are referenced as C1, C2, C3, etc. If the first row contains headers, header notation may also be used. For example, if a column header is Price, reference it as [Price]. |
| Calculate Error Value (optional) | The value to display when a formula fails. |
Remarks:
- Default values:
- Calculate Error Value: returns a blank result.
Examples:
The examples provided below are based on fictitious data for illustrative purposes only.
> Example 1: Simple evaluations returning TRUE or FALSE
Range (A1:C3):
| 1 | 2 | 3 |
| 4 | 5 | 6 |
| 7 | 8 | 9 |
Determine whether each entry is divisible by 2.
Formula:
=Qx.Transform(A1:C3, "mod(C1,2)=0, mod(C2,2)=0, mod(C3,2)=0")Result:
| FALSE | TRUE | FALSE |
| TRUE | FALSE | TRUE |
| FALSE | TRUE | FALSE |
> Example 2: Using Transform with a K=Series
A K=Series allows you to apply the same instruction to multiple adjacent columns. The word Columns refers to the total number of columns. For example, to reference the second‑last column, use Columns-1.
Range (A1:C3):
| 1 | 2 | 3 |
| 4 | 5 | 6 |
| 7 | 8 | 9 |
Determine whether each entry is divisible by 2.
Formula:
=Qx.Transform(A1:C3, "k=series(1,Columns):mod(Ck,2)=0")Result:
| FALSE | TRUE | FALSE |
| TRUE | FALSE | TRUE |
| FALSE | TRUE | FALSE |
> Example 3: 2‑point rolling average using RC reference syntax
Range (A1:C3):
| 1 | 2 | 3 |
| 4 | 5 | 6 |
| 7 | 8 | 9 |
Calculate the rolling average of pairs of consecutive values in column 1. If a row does not have enough data points, return the text Insufficient data points.
Formula:
=Qx.Transform(A1:C3, "average(r(line-1)C(1):R(line)C(1))", "Insufficient data points")Result:
| Insufficient data points |
| 2.5 |
| 5.5 |
> Example 4: Continued calculation using output. syntax
Range (A1:C3):
| 1 | 2 | 3 |
| 4 | 5 | 6 |
| 7 | 8 | 9 |
Calculation is required where:
- Column 1:
Sum the current row with the previous row. For the first row, take the value as is:if(line=1, C1, sum(r(line-1)C(1):r(line)C(1))) - Column 2:
Divide the original column value by the newly calculated value, usingoutput.syntax to reference the result from Column 1. Round up to 2 decimal places:roundup(C1/output.C1 * 100, 2)
Formula:
=Qx.Transform(A1:C3, "if(line=1, C1, sum(r(line-1)C(1):r(line)C(1))), roundup(C1/output.C1 * 100, 2)")Result:
| 1 | 1.00 |
| 5 | 0.80 |
| 11 | 0.64 |