### SQL query example with conversions and JavaScript methods

Source: https://docs.stiltsoft.com/tfac/cloud/general-syntax-and-sql-functions-available-42246251.html

Example SQL query using conversions and JavaScript methods on columns from table T*.

```sql
SELECT 
T1.'String'->length AS 'Length',
T1.'String'->split("b")->0 AS 'Splitting',
T1.'Number'->toFixed(1) AS 'Rounding to tenth',
T1.'Date'::Date->getFullYear() AS 'Full year'
FROM T*
```

--------------------------------

### BEGIN

Source: https://docs.stiltsoft.com/tfac/cloud/general-syntax-and-sql-functions-available-42246251.html?scroll-versions%3Avariant-name=cloud

Starts code block for WHILE and IF statements.

```APIDOC
## BEGIN

### Description
Starts code block for WHILE and IF statements.

### Type
LOGIC LOOP

### Syntax
BEGIN ... END

### Example
IF condition THEN BEGIN ... END
```

--------------------------------

### LIKE pattern examples for matching values

Source: https://docs.stiltsoft.com/tfac/dc-server/searching-for-a-specified-pattern-in-a-column-188621395.html?scroll-versions%3Avariant-name=cloud

These examples show how to use the LIKE operator with different wildcard patterns to match values in a column. They are useful for understanding how to construct patterns for various search conditions.

```text
LIKE "a%" - Finds any values that start with "a"  
LIKE "%a" - Finds any values that end with "a"  
LIKE "_r%" - Finds any values that have "r" in the second position  
LIKE "a_%" - Finds any values that start with "a" and are at least 2 characters in length  
LIKE "a__%" - Finds any values that start with "a" and are at least 3 characters in length  
LIKE "B%s" - Finds any values that start with "B" and ends with "s"
```

--------------------------------

### LIKE pattern examples for SQL queries

Source: https://docs.stiltsoft.com/tfac/cloud/searching-for-a-specified-pattern-in-a-column-188621395.html

These are examples of SQL LIKE patterns that can be used in the Table Transformer macro to find values matching specific conditions, such as starting with 'a', ending with 'a', or having a character at a specific position. They are listed as reference patterns.

```sql
LIKE "a%" - Finds any values that start with "a"
```

```sql
LIKE "%a" - Finds any values that end with "a"
```

```sql
LIKE "_r%" - Finds any values that have "r" in the second position
```

```sql
LIKE "a_%" - Finds any values that start with "a" and are at least 2 characters in length
```

```sql
LIKE "a__%" - Finds any values that start with "a" and are at least 3 characters in length
```

```sql
LIKE "B%s" - Finds any values that start with "B" and ends with "s"
```

--------------------------------

### FORMATWIKI function syntax example

Source: https://docs.stiltsoft.com/tfac/dc-server/using-beautiful-colors-from-the-atlassian-design-system-203719047.html?scroll-versions%3Avariant-name=cloud

Shows the syntax for the FORMATWIKI function with a design token and fallback color. The token --ds-background-accent-red-subtlest is from the Atlassian Design System, and 'red' is the default Confluence color used if the token is unavailable.

```text
FORMATWIKI("{cell:background-color=var(--ds-background-accent-red-subtlest, red)}", T1.'Amount of Transactions', "{cell}")
```

--------------------------------

### SEQUENCE

Source: https://docs.stiltsoft.com/tfac/cloud/functions-available-in-table-spreadsheet-107742583.html

Creates a grid of sequential numbers with configurable start and step values.

```APIDOC
## SEQUENCE

### Description
Returns a grid of sequential numbers starting at a specified start value and increasing by a specified step size. By default, the sequence starts at and increases by 1.

### Syntax
SEQUENCE(rows, [columns], [start], [step])

### Parameters
- **rows** (rangenumber) - Required - The number of rows in the function's resulting grid. Example: 1
- **columns** (rangenumber) - Optional - The number of columns in the function's resulting grid. If omitted, the result grid will have 1 column. Example: 1
- **start** (rangenumber) - Optional - The number, at which to start the sequence. If omitted, the sequence will start at 1. Example: 1
- **step** (rangenumber) - Optional - The amount each value in the sequence will differ by. If omitted, each value will differ by 1. Example: 1

### Return Value
Returns a grid of sequential numbers.
```

--------------------------------

### SQL query when column labels differ

Source: https://docs.stiltsoft.com/tfac/cloud/default-automatic-presets-42241562.html?scroll-versions%3Avariant-name=dc-server

Use this query to join tables when the matching columns have different labels. The example matches Table 1's 'Id' with Table 2's '#' and Table 3's 'N'.

```sql
SELECT
   T1.'Id',
   T1.'Full name',
   T2.'Login',
   T3.'Personal space',
   T3.'Create space',
   T3.'Confluence admin',
   T3.'System admin' 
FROM T1 
JOIN T2 ON T1.'Id' = T2.'#' 
JOIN T3 ON T1.'Id' = T3.'N'
```

--------------------------------

### CHOOSE

Source: https://docs.stiltsoft.com/tfac/cloud/functions-available-in-table-spreadsheet-107742583.html

Documentation for the CHOOSE function in Table Spreadsheet.

```APIDOC
## CHOOSE

### Description
Returns an element from a list of choices based on index.

### Parameters
- **index** (rangenumber) - Required - Which choice (of the up to 30 provided) to return. Example: 2
- **choice1** (rangeall) - Required - A potential value to return. Required. May be a reference to a cell or an individual value. Example: "A"
- **choice2** (rangeall) - Optional - Additional values among which to choose. Example: "B"

### Returns
An element from a list of choices based on index.
```

--------------------------------

### MID

Source: https://docs.stiltsoft.com/tfac/cloud/general-syntax-and-sql-functions-available-42246251.html?scroll-versions%3Avariant-name=cloud

Extracts a substring from a string (starting at any position).

```APIDOC
## MID

### Description
Extracts a substring from a string (starting at any position).

### Syntax
MID(string, start, length)
```

--------------------------------

### AT

Source: https://docs.stiltsoft.com/tfac/cloud/general-syntax-and-sql-functions-available-42246251.html?scroll-versions%3Avariant-name=cloud

No description provided.

```APIDOC
## AT

### Description
No description provided.

### Type
TIME
```

--------------------------------

### SQL query to calculate workdays (basic)

Source: https://docs.stiltsoft.com/tfac/cloud/calculating-the-number-of-workdays-in-a-period-of-time-188618361.html

Use this SQL query in the Table Transformer macro to count workdays between 'Start date' and 'End date' columns in table T1. It calculates total days, subtracts weekends, and handles edge cases for start day and remainder.

```SQL
SELECT *,
'Days' -
2 * (('Days' / 7)::integer) -
CASE
	WHEN 'Days' % 7 = 0 THEN 0
	WHEN 'Start date'::Date->getDay() = 0 THEN 1
	WHEN 'Start date'::Date->getDay() + 'Days' % 7 = 7 THEN 1
	WHEN 'Start date'::Date->getDay() + 'Days' % 7 > 7 THEN 2
	ELSE 0
END AS 'Work days'
FROM
(SELECT *,
ROUND((T1.'End date' - T1.'Start date') / "1d") + 1 AS 'Days'
FROM T1)
```

--------------------------------

### SQL query for Table Transformer to get latest velocity

Source: https://docs.stiltsoft.com/tfac/cloud/calculating-velocity-42242152.html

Use this SQL query in the Table Transformer macro to retrieve the latest velocity value from the table. It selects the top 1 row ordered by sprint descending. This is used in the bonus section to get the most recent velocity.

```SQL
SELECT TOP 1 'Velocity' FROM T1 ORDER BY 'sprint' DESC
```

--------------------------------

### SQL query to format due dates as quarters using TEXT and FORMATWIKI

Source: https://docs.stiltsoft.com/tfac/cloud/formatting-as-strings-188621409.html?scroll-versions%3Avariant-name=cloud

Use this SQL query in the Table Transformer macro to add a 'Due Quarter' column that displays the quarter (Q2, Q3, Q4) based on the month in the 'Due Date' column. The TEXT function converts the date to a string for pattern matching with LIKE, and FORMATWIKI applies the quarter label. Ensure the table is pasted in the macro body and the query is entered in the SQL query tab.

```sql
SELECT *,
  CASE WHEN TEXT(T1.'Due Date') LIKE "%Jun%"
  THEN FORMATWIKI("*","Q2","*")
  WHEN TEXT(T1.'Due Date') LIKE "%Sep%"
  THEN FORMATWIKI("*","Q3","*")
  WHEN TEXT(T1.'Due Date') LIKE "%Nov%"
  THEN FORMATWIKI("*","Q4","*")
  END as 'Due Quarter'
FROM T1
```

--------------------------------

### SQL query to create clickable links with FORMATWIKI and CONCAT

Source: https://docs.stiltsoft.com/tfac/cloud/creating-clickable-links-188620180.html

Use this SQL query in the Table Transformer macro's custom transformation to create clickable links from multiple text pieces. It uses FORMATWIKI to format the link and CONCAT to combine the city and folder values into a URL. The query joins tables T1 and T2 on the 'Name' column.

```SQL
SELECT T1.'Name', T1.'Phone Number', T1.'City', FORMATWIKI("[" + CONCAT("https://", T1.'City', ".mycompany.com/", T2.'Folder') , "]") AS 'Workspace', FORMATWIKI("[The link|" , CONCAT("https://", T1.'City', ".mycompany.com/", T2.'Folder') , "]") AS 'Workspace 2' FROM T1 OUTER JOIN T2 ON T1.'Name' = T2.'Name'
```

--------------------------------

### WORKDAY

Source: https://docs.stiltsoft.com/tfac/cloud/functions-available-in-table-spreadsheet-107742583.html?scroll-versions%3Avariant-name=dc-server

Calculates the date after a number of working days from a specified start date.

```APIDOC
## WORKDAY

### Description
Calculates the date after a number of working days from a specified start date.

### Parameters
- **start_date** (rangeall) - Required - The date from which to begin counting. Example: DATE(1969
- **num_days** (rangenumber) - Required - The number of working days to advance from `start_date`. If negative, counts backwards. Example: 7
- **holidays** (rangeall) - Optional - A range or array constant containing the dates to consider holidays. Example: 16)

### Return Value
Number of working days from start date.
```

--------------------------------

### CEIL

Source: https://docs.stiltsoft.com/tfac/cloud/general-syntax-and-sql-functions-available-42246251.html?scroll-versions%3Avariant-name=cloud

Gets the smallest integer which is greater than, or equal to, the specified numeric expression.

```APIDOC
## CEIL

### Description
Gets the smallest integer which is greater than, or equal to, the specified numeric expression.

### Type
MATH

### Syntax
CEIL(number)

### Parameters
- **number** (numeric) - Required - The numeric expression.

### Example
CEIL(4.2) returns 5
```

--------------------------------

### AND

Source: https://docs.stiltsoft.com/tfac/cloud/functions-available-in-table-spreadsheet-107742583.html?scroll-versions%3Avariant-name=cloud

Returns true if all of the provided arguments are logically true, and false if any of the provided arguments are logically false.

```APIDOC
## AND

### Description
Returns true if all of the provided arguments are logically true, and false if any of the provided arguments are logically false.

### Parameters
- **logical_expression1** (rangeall) - Required - An expression or reference to a cell containing an expression that represents some logical value, i.e. `TRUE` or `FALSE`, or an expression that can be coerced to a logical value. Example: A2 = "foo"
- **logical_expression2** (rangeall) - Optional - More expressions that represent logical values. Example: A3 = "bar"

### Example
AND(A2 = "foo", A3 = "bar")
```

--------------------------------

### SQL query to set custom variables and format a project link

Source: https://docs.stiltsoft.com/tfac/cloud/custom-user-variables-203729910.html?scroll-versions%3Avariant-name=cloud

Use this SQL query in the Table Transformer macro's Custom transformation to set a URL prefix and a project value from the table, then format a clickable link. The result is a link like https://www.123.com/portal/TFCS-324.

```SQL
SET @var1 = "https://www.123.com/portal/";
SET @var2 = (SELECT T1.'Project' FROM T1);

SELECT FORMATWIKI("[Link|", @var1, @var2, "]") AS 'Project Link';
```

--------------------------------

### OFFSET

Source: https://docs.stiltsoft.com/tfac/cloud/functions-available-in-table-spreadsheet-107742583.html?scroll-versions%3Avariant-name=cloud

Returns a range reference shifted a specified number of rows and columns from a starting cell reference.

```APIDOC
## OFFSET

### Description
Returns a range reference shifted a specified number of rows and columns from a starting cell reference.

### Parameters
- **cell_reference** (range) - Required - The starting point from which to count the offset rows and columns. Example: A2
- **offset_rows** (rangenumber) - Required - The number of rows to offset by. Example: 3
- **offset_columns** (rangenumber) - Required - The number of columns to offset by. Example: 4
- **height** (rangenumber) - Optional - The height of the range to return starting at the offset target. Example: 2
- **width** (rangenumber) - Optional - The width of the range to return starting at the offset target. Example: 2

### Return Value
A range reference offset relative to a cell.

### Argument Count
2 to 5 arguments.
```

--------------------------------

### OFFSET

Source: https://docs.stiltsoft.com/tfac/cloud/functions-available-in-table-spreadsheet-107742583.html?scroll-versions%3Avariant-name=dc-server

Returns a range reference shifted a specified number of rows and columns from a starting cell reference.

```APIDOC
## OFFSET

### Description
Returns a range reference shifted a specified number of rows and columns from a starting cell reference.

### Function Signature
OFFSET(cell_reference, offset_rows, offset_columns, [height], [width])

### Parameters
- **cell_reference** (range) - Required - The starting point from which to count the offset rows and columns. Example: A2
- **offset_rows** (rangenumber) - Required - The number of rows to offset by. Example: 3
- **offset_columns** (rangenumber) - Required - The number of columns to offset by. Example: 4
- **height** (rangenumber) - Optional - The height of the range to return starting at the offset target. Example: 2
- **width** (rangenumber) - Optional - The width of the range to return starting at the offset target. Example: 2

### Return Value
A range reference offset relative to a cell.

### Example
=OFFSET(A2, 3, 4, 2, 2)
```

--------------------------------

### FROM

Source: https://docs.stiltsoft.com/tfac/cloud/general-syntax-and-sql-functions-available-42246251.html?scroll-versions%3Avariant-name=cloud

Specifies which table to select or delete data from.

```APIDOC
## FROM

### Description
Specifies which table to select or delete data from.

### Syntax
FROM table_name

### Parameters
- **table_name** (string) - Required - The name of the table.
```

--------------------------------

### GET /rest/table-filter/1.0/service/export/csv

Source: https://docs.stiltsoft.com/tfac/cloud/how-to-export-macros-and-results-42239877.html?scroll-versions%3Avariant-name=dc-server

Exports a macro's table to CSV format via REST API. Requires page ID and optionally macro ID, macro name, macro number, and delimiter. Returns a CSV file.

```APIDOC
## GET /rest/table-filter/1.0/service/export/csv

### Description
Exports the table of a specified macro on a given page to CSV format. The endpoint accepts parameters to identify the macro and returns the CSV file.

### Method
GET

### Endpoint
/rest/table-filter/1.0/service/export/csv

### Parameters
#### Query Parameters
- **pageId** (integer) - Required - The page ID number where the required macro is located.
- **id** (string) - Optional - Unique identifier of the required macro on the page.
- **macroName** (string) - Optional - The name of the required macro on the page. Default is "table-filter". Available values: "table-filter", "pivot-table", "table-chart", "table-joiner" (for Table Transformer), "table-excerpt-include".
- **macroNumber** (integer) - Optional - The serial number of the required macro on the page. Default is 1.
- **delimiter** (string) - Optional - The output data delimiter. Default is comma.

### Request Example
http://my-confluence-instance.com/rest/table-filter/1.0/service/export/csv?pageId=1147061&id=69a5f057-38eb-4af5-989b-e9aba26ea1e7&macroName=table-joiner&macroNumber=2&delimiter=;

### Response
#### Success Response (200)
Returns the CSV file.

#### Error Response (404)
Returned if the macro is not found on the specified page.

```

--------------------------------

### GETDATE

Source: https://docs.stiltsoft.com/tfac/cloud/general-syntax-and-sql-functions-available-42246251.html?scroll-versions%3Avariant-name=cloud

Returns the current system date and time.

```APIDOC
## GETDATE

### Description
Returns the current system date and time.

### Syntax
GETDATE()
```

--------------------------------

### SMALL

Source: https://docs.stiltsoft.com/tfac/cloud/functions-available-in-table-spreadsheet-107742583.html

Documentation for the SMALL function.

```APIDOC
## SMALL

### Description
Returns the nth smallest element from a data set, where n is user-defined.

### Parameters
- **data** (range) - Required - The array or range containing the dataset to consider.
- **n** (rangenumber) - Required - The rank from smallest to largest of the element to return.

### Argument Count
[2,2]
```

--------------------------------

### REST API CSV export request URL

Source: https://docs.stiltsoft.com/tfac/cloud/how-to-export-macros-and-results-42239877.html?scroll-versions%3Avariant-name=dc-server

Example URL for exporting a table to CSV via the /rest/table-filter/1.0/service/export/csv endpoint. Requires pageId and optionally id, macroName, macroNumber, and delimiter. The user must have view permission on the page.

```http
http://my-confluence-instance.com/rest/table-filter/1.0/service/export/csv?pageId=1147061&id=69a5f057-38eb-4af5-989b-e9aba26ea1e7&macroName=table-joiner&macroNumber=2&delimiter=;
```

--------------------------------

### SQL query for monthly budget analysis

Source: https://docs.stiltsoft.com/tfac/cloud/accounting-for-the-monthly-budget-188618528.html?scroll-versions%3Avariant-name=cloud

Use this SQL query in the Table Transformer macro to calculate the difference between actual and budget, the percentage of budget used, and a status indicator. The query uses FORMATWIKI to display status labels with colors: Green for Under budget, Red for Over budget, and Yellow for On budget.

```SQL
SELECT *,
(T1.'Actual'- T1.'Budget') AS 'Difference, $',
(T1.'Actual' / T1.'Budget' * 100) AS '% Budget',
CASE
WHEN (T1.'Actual' / T1.'Budget' * 100) < 100
THEN FORMATWIKI("{status:colour=Green|title=Under budget}")
WHEN (T1.'Actual' / T1.'Budget' * 100) > 100
THEN FORMATWIKI("{status:colour=Red|title=Over budget}")
ELSE FORMATWIKI("{status:colour=Yellow|title=On budget}")
END AS 'Indicator'
FROM T*
```

--------------------------------

### SQL query to calculate workdays excluding holidays (advanced)

Source: https://docs.stiltsoft.com/tfac/cloud/calculating-the-number-of-workdays-in-a-period-of-time-188618361.html

Use this SQL query in the Table Transformer macro to count workdays between 'Start date' and 'End date' while excluding holidays from table T2. It subtracts weekends and holidays that fall on weekdays.

```SQL
SELECT *,
'Days' - 2 * (('Days' / 7)::integer) -
CASE
	WHEN 'Days' % 7 = 0 THEN 0
	WHEN 'Start date'::Date->getDay() = 0 THEN 1
	WHEN 'Start date'::Date->getDay() + 'Days' % 7 = 7 THEN 1
	WHEN 'Start date'::Date->getDay() + 'Days' % 7 > 7 THEN 2
	ELSE 0
END
- 
(SELECT COUNT(*) FROM 
(SELECT *,'Holiday'::Date->getDay() AS 'Day' FROM T2) 
WHERE 'Holiday' >= TT.'Start date' AND 'Holiday' <= TT.'End date'
AND 'Day' > 0 AND 'Day' < 6)
AS 'Work days'
FROM
(SELECT *, ROUND(('End date' - 'Start date') / "1d") + 1 AS 'Days' FROM T1) AS TT
```

--------------------------------

### ARRAY

Source: https://docs.stiltsoft.com/tfac/cloud/general-syntax-and-sql-functions-available-42246251.html?scroll-versions%3Avariant-name=cloud

Creates an array.

```APIDOC
## ARRAY

### Description
Creates an array.

### Type
DATA_MANIPULATING

### Syntax
ARRAY(value1, value2, ...)

### Parameters
- **value1, value2, ...** (any) - Required - Values to include in the array.

### Example
ARRAY(1, 2, 3) creates an array with elements 1, 2, 3.
```

--------------------------------

### SQL query to calculate completion ratio

Source: https://docs.stiltsoft.com/tfac/cloud/calculating-the-completion-ratio-188618522.html?scroll-versions%3Avariant-name=cloud

Use this SQL query in the Table Transformer macro's custom transformation to calculate the completion ratio. It computes the ratio as a percentage using DATEDIFF between start and end dates, then clamps the result to 0% or 100% if out of range.

```SQL
SELECT *,
CASE WHEN 'Completion Ratio'>100
THEN "100%"
WHEN 'Completion Ratio'<0
THEN "0%"
ELSE ROUND('Completion Ratio')+"%"
END  AS 'Completion Ratio'
FROM 
(SELECT *, 
DATEDIFF(DAY,'Start Date',"today")/
DATEDIFF(DAY, 'Start Date', 'End Date')*100 AS 'Completion Ratio'
FROM T1)
```

--------------------------------

### SQL query to count issues by status with clickable Jira links

Source: https://docs.stiltsoft.com/tfac/cloud/creating-clickable-links-to-the-jira-issues-188618489.html

Use this SQL query in the Table Transformer macro to count issues by status and create clickable links to Jira. The query uses FORMATWIKI to construct a URL with the issue keys, and includes a total row via UNION ALL CORRESPONDING.

```sql
SELECT 'Status',
 FORMATWIKI("["+ COUNT(*) +"|" + 
		   "https://jira.stiltsoft.com/issues/?jql=issuekey%20in%20(" + 
		   ARRAY(T1.'Key') + ")" + "]")
AS 'COUNT'
FROM T1 GROUP BY T1.'Status'
UNION ALL CORRESPONDING
SELECT "Total" AS 'Status',
FORMATWIKI("["+ COUNT(*) +"|" + 
		   "https://jira.stiltsoft.com/issues/?jql=issuekey%20in%20(" + 
		   ARRAY(T1.'Key') + ")" + "]") AS 'COUNT'
FROM T1
```

--------------------------------

### SQL query with COALESCE() for multi-language field selection

Source: https://docs.stiltsoft.com/tfac/cloud/selecting-fields-columns-of-multiple-languages-188618428.html?scroll-versions%3Avariant-name=cloud

Use this SQL query in the Table Transformer macro's custom transformation to select fields with language-specific names. The COALESCE() function returns the first non-null value, allowing the query to work across different Confluence profile languages. This example maps 'Description' and 'Assignee' between English and German.

```SQL
SELECT COALESCE('Description','Beschreibung') as 'Description',
COALESCE('Assignee','Bearbeiter') as 'Assignee' FROM T1
```

--------------------------------

### OR

Source: https://docs.stiltsoft.com/tfac/dc-server/functions-available-in-table-spreadsheet-107742583.html?scroll-versions%3Avariant-name=dc-server

Returns true if any of the provided arguments are logically true, and false if all of the provided arguments are logically false.

```APIDOC
## OR

### Description
Returns true if any of the provided arguments are logically true, and false if all of the provided arguments are logically false.

### Category
Logical

### Function Name
OR

### Usage
Use this function in a spreadsheet formula to perform a logical OR operation.
```

--------------------------------

### IS

Source: https://docs.stiltsoft.com/tfac/dc-server/general-syntax-and-sql-functions-available-42246251.html?scroll-versions%3Avariant-name=cloud

No description provided.

```APIDOC
## IS

### Description
(No description provided)
```

--------------------------------

### SQL query using SUBSTRING_VIEW to extract content from a macro column

Source: https://docs.stiltsoft.com/tfac/cloud/extracting-uneditable-macros-content-188621424.html

Use this SQL query in the Table Transformer macro's custom transformation to extract a substring from a column (e.g., 'Key') and select other columns. The SUBSTRING_VIEW function preserves the original cell formatting, including links. The syntax is: SUBSTRING_VIEW('YOUR COLUMN NAME', STARTING POSITION NUMBER, THE NUMBER OF CHARACTERS TO DERIVE).

```sql
SELECT SUBSTRING_VIEW(T1.'Key',5,4) as 'OTS Key',
 T1.'Assignee',T1.'T',T1.'Created' FROM T1

/* SYNTAX - SUBSTRING_VIEW('YOUR COLUMN NAME', STARTING POSITION NUMBER, THE NUMBER OF CHARACTERS TO DERIVE) */
```

--------------------------------

### TODAY

Source: https://docs.stiltsoft.com/tfac/cloud/functions-available-in-table-spreadsheet-107742583.html

Documentation for the TODAY function.

```APIDOC
## TODAY

### Description
Returns the current date as a date value.

### Parameters
None.

### Returns
Current date as a date value.
```

--------------------------------

### SQL query to calculate code quality with CASE WHEN

Source: https://docs.stiltsoft.com/tfac/cloud/using-formulas-to-calculate-with-sql-188618335.html?scroll-versions%3Avariant-name=dc-server

Use this SQL query in the Table Transformer macro's custom transformation to calculate defects per 10 lines and code quality. It multiplies the 'Number of defects' by 10 and divides by 'Lines of code added' to get 'Defects per 10 lines', then uses CASE WHEN to output 'Good' if the value is less than 1, otherwise 'Bad'.

```SQL
SELECT
   *,
   (
      'Number of defects' * 10 / 'Lines of code added'
   )
   AS 'Defects per 10 lines',
   CASE
      WHEN
         (
            'Number of defects' * 10 / 'Lines of code added'
         )
         < 1 
      THEN
         "Good" 
      ELSE
         "Bad" 
   END
   AS 'Code quality' 
FROM
   T1
```

--------------------------------

### SQL query using REPLACE_VIEW to replace column values

Source: https://docs.stiltsoft.com/tfac/cloud/replacing-uneditable-macros-content-188621431.html

Use this SQL query in the Table Transformer macro's Custom transformation to replace values in a column. The example replaces 'Done' with 'Resolved' in the 'Status' column. The comment shows the syntax: REPLACE_VIEW('YOUR COLUMN NAME', "SOURCE DATA TO REPLACE", "NEW DATA REPLACING THE OLD"). The function preserves the original cell formatting.

```SQL
SELECT *, REPLACE_VIEW(T1.'Status',"Done","Resolved") as 'Status' FROM T1 

/* SYNTAX - REPLACE_VIEW('YOUR COLUMN NAME', "SOURCE DATA TO REPLACE","NEW DATA REPLACING THE OLD") */
```

--------------------------------

### AVERAGEIFS

Source: https://docs.stiltsoft.com/tfac/cloud/functions-available-in-table-spreadsheet-107742583.html

Documentation for the AVERAGEIFS function.

```APIDOC
## AVERAGEIFS

### Description
Returns the average of a range depending on multiple criteria.

### Parameters
- **average_range** (rangeall) - Required - The range to average.
- **criteria_range1** (rangeall) - Required - The range to check against `criterion1`.
- **criterion1** (rangeall) - Required - The pattern or test to apply to `criteria_range1`.
- **criteria_range2** (rangeall) - Required - Additional ranges to check.

### Example
`AVERAGEIFS(A1:A10, B1:B10, ">20", C1:C10, ...)`
```

--------------------------------

### BY

Source: https://docs.stiltsoft.com/tfac/cloud/general-syntax-and-sql-functions-available-42246251.html?scroll-versions%3Avariant-name=cloud

Used with ORDER BY, GROUP BY.

```APIDOC
## BY

### Description
Used with ORDER BY, GROUP BY.

### Type
AGGREGATION ORDERING

### Syntax
ORDER BY column BY ... / GROUP BY column BY ...

### Parameters
- **column** (string) - Required - The column to order or group by.

### Example
SELECT * FROM table ORDER BY column BY ...
```

--------------------------------

### SQL query to add tooltips with FORMATMARKDOWN

Source: https://docs.stiltsoft.com/tfac/cloud/adding-tooltips-188623229.html?scroll-versions%3Avariant-name=cloud

Use this SQL query in the Table Transformer macro's custom transformation to add tooltips to the 'Number of Issues' and 'Project Manager' columns. The 'Number of Issues' tooltip is static, while the 'Project Manager' tooltip is dynamic, taken from the 'Status of the Project Manager' column. Ensure the macro body contains the table or macros outputting tables.

```SQL
SELECT 'Project',
FORMATMARKDOWN("[" + T1.'Number of Issues' + "](# \"The cancelled issues are not counted\")")
AS 'Number of Issues',
FORMATMARKDOWN("[" + T1.'Project Manager' + "](# \"" + T1.'Status of the Project Manager' + "\")")
AS 'Project Manager'
FROM T*
```

--------------------------------

### AVERAGE

Source: https://docs.stiltsoft.com/tfac/cloud/functions-available-in-table-spreadsheet-107742583.html

Documentation for the AVERAGE function.

```APIDOC
## AVERAGE

### Description
Returns the numerical average value in a dataset, ignoring text.

### Parameters
- **value1** (rangeall) - Required - The first value or range to consider when calculating the average value.
- **value2** (rangeall) - Optional - Additional values or ranges to consider when calculating the average value.

### Returns
Numerical average value in a dataset, ignoring text.
```

--------------------------------

### LOG

Source: https://docs.stiltsoft.com/tfac/cloud/general-syntax-and-sql-functions-available-42246251.html?scroll-versions%3Avariant-name=cloud

Returns the natural logarithm.

```APIDOC
## LOG

### Description
Returns the natural logarithm.

### Syntax
LOG(number)
```

--------------------------------

### JOIN

Source: https://docs.stiltsoft.com/tfac/cloud/functions-available-in-table-spreadsheet-107742583.html

Documentation for the JOIN function in Table Spreadsheet.

```APIDOC
## JOIN

### Description
Concatenates the elements of one or more one-dimensional arrays using a specified delimiter.

### Parameters
#### Arguments
- **delimiter** (rangeall) - Required - The character or string to place between each concatenated value. Example: " and-a "
- **value_or_array1** (rangeall) - Required - The value or values to be appended using `delimiter`. Example: {1
- **value_or_array2** (rangeall) - Optional - More values to be appended using `delimiter`. Example: 2

### Returns
Concatenates elements of arrays with delimiter.
```

--------------------------------

### NOW

Source: https://docs.stiltsoft.com/tfac/cloud/functions-available-in-table-spreadsheet-107742583.html?scroll-versions%3Avariant-name=dc-server

Returns the current date and time as a date value.

```APIDOC
## NOW

### Description
Returns the current date and time as a date value.

### Parameters
None

### Argument Count
Minimum: 0, Maximum: 0
```

--------------------------------

### NOW

Source: https://docs.stiltsoft.com/tfac/cloud/functions-available-in-table-spreadsheet-107742583.html?scroll-versions%3Avariant-name=cloud

Returns the current date and time as a date value.

```APIDOC
## NOW

### Description
Returns the current date and time as a date value.

### Parameters
None.

### Return Value
A date value representing the current date and time.
```

--------------------------------

### CHOOSE

Source: https://docs.stiltsoft.com/tfac/cloud/functions-available-in-table-spreadsheet-107742583.html?scroll-versions%3Avariant-name=dc-server

Returns an element from a list of choices based on index.

```APIDOC
## CHOOSE

### Description
Returns an element from a list of choices based on index.

### Function Signature
CHOOSE(index, choice1, [choice2], ...)

### Parameters
- **index** (rangenumber) - Required - Which choice (of the up to 30 provided) to return. Example: 2
- **choice1** (rangeall) - Required - A potential value to return. Required. May be a reference to a cell or an individual value. Example: "A"
- **choice2** (rangeall) - Optional - Additional values among which to choose. Example: "B"

### Return Value
An element from a list of choices based on index.

### Example
=CHOOSE(2, "A", "B")
```

--------------------------------

### CHOOSE

Source: https://docs.stiltsoft.com/tfac/cloud/functions-available-in-table-spreadsheet-107742583.html?scroll-versions%3Avariant-name=cloud

Returns an element from a list of choices based on index.

```APIDOC
## CHOOSE

### Description
Returns an element from a list of choices based on index.

### Parameters
- **index** (rangenumber) - Required - Which choice (of the up to 30 provided) to return. Example: 2
- **choice1** (rangeall) - Required - A potential value to return. Required. May be a reference to a cell or an individual value. Example: "A"
- **choice2** (rangeall) - Optional - Additional values among which to choose. Example: "B"

### Return Value
An element from a list of choices based on index.

### Argument Count
2 to 255 arguments.
```

--------------------------------

### SQL query using FORMATWIKI and ESCAPEMARKUP

Source: https://docs.stiltsoft.com/tfac/cloud/escaping-special-characters-for-wiki-markup-188620130.html

Use this SQL query in the Table Transformer macro's custom transformation to format a column as wiki markup and escape special characters. The query selects all columns, applies FORMATWIKI to the 'Description' column, and also applies FORMATWIKI with ESCAPEMARKUP to wrap the escaped description in asterisks. This is useful when you need to display wiki markup literally or ensure special characters are not interpreted.

```SQL
SELECT *, FORMATWIKI(T1.'Description') AS 'FORMATWIKI', FORMATWIKI("*",ESCAPEMARKUP(T1.'Description'),"*") AS 'FORMATWIKI+ESCAPEMARKUP' FROM T1
```

--------------------------------

### PROPER

Source: https://docs.stiltsoft.com/tfac/cloud/functions-available-in-table-spreadsheet-107742583.html

Capitalizes each word in a specified string.

```APIDOC
## PROPER

### Description
Capitalizes each word in a specified string.

### Parameters
- **text_to_capitalize** (rangeall) - Required - The text which will be returned with the first letter of each word in uppercase and all other letters in lowercase. Example: "united states"
```

--------------------------------

### CASE

Source: https://docs.stiltsoft.com/tfac/cloud/general-syntax-and-sql-functions-available-42246251.html?scroll-versions%3Avariant-name=cloud

Creates different outputs based on conditions.

```APIDOC
## CASE

### Description
Creates different outputs based on conditions.

### Type
DATA_MANIPULATING LOGIC

### Syntax
CASE WHEN condition THEN result ... ELSE else_result END

### Parameters
- **condition** (boolean) - Required - The condition to evaluate.
- **result** (any) - Required - The result if condition is true.
- **else_result** (any) - Optional - The result if no condition is true.

### Example
CASE WHEN column > 10 THEN 'High' ELSE 'Low' END
```

--------------------------------

### SQL query for visualizing progression data as bars

Source: https://docs.stiltsoft.com/tfac/cloud/visualizing-the-progression-data-in-rows-as-bars-188620385.html?scroll-versions%3Avariant-name=cloud

Use this SQL query in the Table Transformer macro to visualize progression data as bars. It sets a variable for the 100% progression value, calculates the progress percentage, and formats it as a bar using the FORMATWIKI function.

```sql
SET @maxProgress = (SELECT FORMATNUMBER(40) FROM T1); 
/* set the number representing the 100% progression in this variable
or use MAX('Your Column Name') instead of the FORMATNUMBER() function to determine the value */

SELECT *, FORMATWIKI("{cell:background-image: linear-gradient(to right, #4caf50, #4caf50);
background-repeat: no-repeat;background-size:"+ 'Progress Bar' +" 50%;background-position-y: 50%;}", 
'Progress Bar', "{cell}") AS 'Progress Bar' FROM 
(SELECT *, T1.'Progress'/@maxProgress * 100 + "%" AS 'Progress Bar' FROM T1);
```

--------------------------------

### LIKE

Source: https://docs.stiltsoft.com/tfac/cloud/general-syntax-and-sql-functions-available-42246251.html?scroll-versions%3Avariant-name=cloud

Searches for a specified pattern in a column.

```APIDOC
## LIKE

### Description
Searches for a specified pattern in a column.

### Syntax
column_name LIKE pattern
```

--------------------------------

### EXACT

Source: https://docs.stiltsoft.com/tfac/cloud/functions-available-in-table-spreadsheet-107742583.html

Documentation for the EXACT function in Table Spreadsheet.

```APIDOC
## EXACT

### Description
Tests whether two strings are identical.

### Parameters
#### Arguments
- **string1** (rangeall) - Required - The first string to compare. Example: A1
- **string2** (rangeall) - Required - The second string to compare. Example: A2

### Returns
Tests whether two strings are identical.
```

--------------------------------

### LEFT

Source: https://docs.stiltsoft.com/tfac/cloud/functions-available-in-table-spreadsheet-107742583.html?scroll-versions%3Avariant-name=cloud

Returns a substring from the beginning of a specified string.

```APIDOC
## LEFT

### Description
Returns a substring from the beginning of a specified string.

### Parameters
- **string** (rangeall) - Required - The string from which the left portion will be returned. Example: A2
- **number_of_characters** (rangenumber) - Required - The number of characters to return from the left side of `string`. Example: 2

### Return
Substring from beginning of specified string.
```

--------------------------------

### AVERAGEIF

Source: https://docs.stiltsoft.com/tfac/cloud/functions-available-in-table-spreadsheet-107742583.html

Documentation for the AVERAGEIF function.

```APIDOC
## AVERAGEIF

### Description
Returns the average of a range depending on criteria.

### Parameters
- **criteria_range** (rangeall) - Required - The range to check against `criterion`.
- **criterion** (rangeall) - Required - The pattern or test to apply to `criteria_range`.
- **average_range** (rangeall) - Optional - The range to average. If not included, `criteria_range` is used for the average instead.

### Example
`AVERAGEIF(A1:A10, ">20", B1:B10)`
```

--------------------------------

### SQL query for Table Transformer to calculate completion ratio

Source: https://docs.stiltsoft.com/tfac/cloud/building-a-gantt-chart-42242448.html

Use this SQL query in the Table Transformer macro to compute a 'Completion Ratio' column based on the Jira Issues macro columns. It sets 'Done' to 100%, 'To Do' to 0%, and calculates a percentage for other statuses using 'Remaining Estimate' and 'Time Spent'. This step is optional if you already have percentage columns or don't need progress bars.

```SQL
SELECT *,
CASE WHEN 'Status' = "Done" 
THEN "100%" 
WHEN 'Status' = "To Do" 
THEN "0%" 
ELSE (100 - ('Remaining Estimate' / 'Time Spent' * 100) + "%") 
END AS 'Completion Ratio'
FROM T*
```

--------------------------------

### DATE

Source: https://docs.stiltsoft.com/tfac/cloud/functions-available-in-table-spreadsheet-107742583.html

Creates a date from year, month, and day components.

```APIDOC
## DATE

### Description
Converts a provided year, month, and day into a date.

### Parameters
- **year** (rangenumber) - Required - The year component of the date.
- **month** (rangenumber) - Required - The month component of the date.
- **day** (rangenumber) - Required - The day component of the date.

### Return Value
Converts year/month/day into a date.
```

--------------------------------

### SQL query to change font size and style with FORMATWIKI

Source: https://docs.stiltsoft.com/tfac/cloud/changing-font-size-style-188620124.html

Use this SQL query in the Table Transformer macro's custom transformation to change the font size and style of specific columns. The query uses FORMATWIKI with CSS-like syntax to set font-size and font-weight for each cell. Ensure the macro is inserted and configured as described in the steps.

```sql
SELECT FORMATWIKI("{cell:font-size=50px|font-weight=bold}", T1.'Transaction Type', "{cell}")
AS 'Transaction Type',
FORMATWIKI("{cell:font-size: 30px; font-weight: bold;}", T1.'Service Agent', "{cell}")
AS 'Service Agent' FROM T*
```

--------------------------------

### AVERAGEA

Source: https://docs.stiltsoft.com/tfac/cloud/functions-available-in-table-spreadsheet-107742583.html

Documentation for the AVERAGEA function.

```APIDOC
## AVERAGEA

### Description
Returns the numerical average value in a dataset.

### Parameters
- **value1** (rangeall) - Required - The first value or range to consider when calculating the average value.
- **value2** (rangeall) - Optional - Additional values or ranges to consider when calculating the average value.

### Returns
Numerical average value in a dataset.
```

--------------------------------

### BINOM_INV

Source: https://docs.stiltsoft.com/tfac/cloud/functions-available-in-table-spreadsheet-107742583.html

Documentation for the BINOM_INV function.

```APIDOC
## BINOM_INV

### Description
Calculates the smallest value for which the cumulative binomial distribution is greater than or equal to a specified criteria.

### Parameters
- **num_trials** (rangenumber) - Required - The number of independent trials.
- **prob_success** (rangenumber) - Required - The probability of success in any given trial.
- **target_prob** (rangenumber) - Required - The desired threshold probability.

### Returns
Inverse cumulative binomial distribution function.
```

--------------------------------

### Pivot aggregated data to final layout

Source: https://docs.stiltsoft.com/tfac/cloud/aggregating-by-headers-188621440.html

Second SQL query to pivot the previously created layout, using FIRST('Dev-stage') for each 'Value'. This completes the aggregation by headers.

```sql
SELECT * FROM T1
PIVOT (FIRST ('Dev-stage') for 'Value')
```

--------------------------------

### STANDARDIZE

Source: https://docs.stiltsoft.com/tfac/cloud/functions-available-in-table-spreadsheet-107742583.html

Documentation for the STANDARDIZE function.

```APIDOC
## STANDARDIZE

### Description
Calculates the normalized equivalent of a random variable given mean and standard deviation of the distribution.

### Parameters
- **value** (rangenumber) - Required - The value of the random variable to normalize.
- **mean** (rangenumber) - Required - The mean of the distribution.
- **standard_deviation** (rangenumber) - Required - The standard deviation of the distribution.

### Argument Count
[3,3]
```

--------------------------------

### PRODUCT

Source: https://docs.stiltsoft.com/tfac/cloud/functions-available-in-table-spreadsheet-107742583.html

Returns the result of multiplying a series of numbers together.

```APIDOC
## PRODUCT

### Description
Returns the result of multiplying a series of numbers together.

### Parameters
- **factor1** (rangenumber) - Required - The first number or range to calculate for the product. Example: A2:A100
- **factor2** (rangenumber) - Optional - More numbers or ranges to calculate for the product. Example: 2

### Argument Count
[1,255]
```

--------------------------------

### RIGHT

Source: https://docs.stiltsoft.com/tfac/cloud/functions-available-in-table-spreadsheet-107742583.html

Documentation for the RIGHT function in Table Spreadsheet.

```APIDOC
## RIGHT

### Description
Returns a substring from the end of a specified string.

### Parameters
#### Arguments
- **string** (rangeall) - Required - The string from which the right portion will be returned. Example: A2
- **number_of_characters** (rangenumber) - Required - The number of characters to return from the right side of `string`. Example: 2

### Returns
A substring from the end of a specified string.
```

--------------------------------

### NOW

Source: https://docs.stiltsoft.com/tfac/cloud/functions-available-in-table-spreadsheet-107742583.html

Current date and time as a date value.

```APIDOC
## NOW

### Description
Returns the current date and time as a date value.

### Parameters
None.
```

--------------------------------

### LEAST

Source: https://docs.stiltsoft.com/tfac/cloud/general-syntax-and-sql-functions-available-42246251.html?scroll-versions%3Avariant-name=cloud

Returns the smallest value of the list of arguments.

```APIDOC
## LEAST

### Description
Returns the smallest value of the list of arguments.

### Syntax
LEAST(value1, value2, ...)
```

--------------------------------

### FIRST

Source: https://docs.stiltsoft.com/tfac/cloud/general-syntax-and-sql-functions-available-42246251.html?scroll-versions%3Avariant-name=cloud

Returns the first value in the group.

```APIDOC
## FIRST

### Description
Returns the first value in the group.

### Type
AGGREGATION

### Syntax
FIRST(column)

### Parameters
- **column** (any) - Required - The column to get the first value from.

### Example
SELECT FIRST(column) FROM table
```

--------------------------------

### Z_TEST

Source: https://docs.stiltsoft.com/tfac/cloud/functions-available-in-table-spreadsheet-107742583.html

Documentation for the Z_TEST function.

```APIDOC
## Z_TEST

### Description
Returns the one-tailed p-value of a Z-test with standard distribution.

### Parameters
- **data** (range) - Required - The array or range containing the dataset to consider.
- **value** (rangenumber) - Required - The test statistic to use in the Z-test.
- **standard_deviation** (rangenumber) - Optional - The standard deviation to assume for the Z-test. If not provided, the standard deviation of the data will be used.

### Returns
One-tailed p-value of a z-test.
```

--------------------------------

### TIME

Source: https://docs.stiltsoft.com/tfac/cloud/functions-available-in-table-spreadsheet-107742583.html

Converts hour/minute/second into a time.

```APIDOC
## TIME

### Description
Converts a provided hour, minute, and second into a time.

### Parameters
- **hour** (rangenumber) - Required - The hour component of the time.
- **minute** (rangenumber) - Required - The minute component of the time.
- **second** (rangenumber) - Required - The second component of the time.

### Example
TIME(11, 40, 59)
```

--------------------------------

### TIME

Source: https://docs.stiltsoft.com/tfac/cloud/functions-available-in-table-spreadsheet-107742583.html?scroll-versions%3Avariant-name=dc-server

Converts a provided hour, minute, and second into a time.

```APIDOC
## TIME

### Description
Converts a provided hour, minute, and second into a time.

### Parameters
- **hour** (rangenumber) - Required - The hour component of the time.
- **minute** (rangenumber) - Required - The minute component of the time.
- **second** (rangenumber) - Required - The second component of the time.

### Argument Count
Minimum: 3, Maximum: 3
```

--------------------------------

### TIME

Source: https://docs.stiltsoft.com/tfac/cloud/functions-available-in-table-spreadsheet-107742583.html?scroll-versions%3Avariant-name=cloud

Converts a provided hour, minute, and second into a time.

```APIDOC
## TIME

### Description
Converts a provided hour, minute, and second into a time.

### Parameters
- **hour** (rangenumber) - Required - The hour component of the time. Example: 11
- **minute** (rangenumber) - Required - The minute component of the time. Example: 40
- **second** (rangenumber) - Required - The second component of the time. Example: 59

### Return Value
A time value.
```

--------------------------------

### IN

Source: https://docs.stiltsoft.com/tfac/cloud/general-syntax-and-sql-functions-available-42246251.html?scroll-versions%3Avariant-name=cloud

Allows you to specify multiple values in a WHERE clause.

```APIDOC
## IN

### Description
Allows you to specify multiple values in a WHERE clause.

### Syntax
column_name IN (value1, value2, ...)
```

--------------------------------

### SQL query using FORMATWIKI to format text in Table Transformer

Source: https://docs.stiltsoft.com/tfac/cloud/text-formatting-188620118.html?scroll-versions%3Avariant-name=dc-server

Use this SQL query in the Table Transformer macro's custom transformation to format text in columns. It applies various text styles such as bold, italics, subscript, superscript, strikethrough, monospace, and block quotation. The query uses FORMATWIKI with different markers for each style.

```SQL
SELECT  FORMATWIKI(T1.'Plain text') as 'Plain Text', 
FORMATWIKI("_",T1.'Italics', "_") as 'Italics',
FORMATWIKI("*",T1.'Bold', "*") as 'Bold',
FORMATWIKI("*","_",T1.'Italics Bold',"_", "*") as 'Italics Bold',
FORMATWIKI("~",T1.'Subscript', "~") as 'Subscript',
FORMATWIKI("^", T1.'Superscript', "^") as 'Superscript',
FORMATWIKI("-",T1.'Strikethrough', "-") as 'Strikethrough',
FORMATWIKI("{{",T1.'Monospace',"}}") as 'Monospace',
FORMATWIKI("{noformat}",T1.'No format',"{noformat}") as 'No format',
FORMATWIKI("+",T1.'Inserted',"+") as 'Inserted',
FORMATWIKI( "\nbq. Example") as 'Block quotation'  
FROM T1
```

--------------------------------

### FORMATWIKI

Source: https://docs.stiltsoft.com/tfac/cloud/general-syntax-and-sql-functions-available-42246251.html?scroll-versions%3Avariant-name=cloud

Applies Confluence Wiki Markup to table cells.

```APIDOC
## FORMATWIKI

### Description
Applies Confluence Wiki Markup to table cells.

### Syntax
FORMATWIKI(value)

### Parameters
- **value** (string) - Required - The value to format with Wiki Markup.
```