Check out our Cloud apps to improve your experience in Jira and Confluence Cloud.
Page tree
Skip to end of metadata
Go to start of metadata

On this page you will learn about:


Using If / Then construct

Use case:

You have a table with the stationary orders. You need to output a new column in the table with the order priority according to the total sum of money: less than $1000 is Low, from $1000 to $2000 is Medium, and more than $2000 is High.

Solution:

  1. Switch the page to the edit mode.
  2. Insert the Table Transformer macro and paste the table or the macros outputting tables within the macro body.
  3. Select the macro and click Edit.
  4. In the Presets tab select Custom transformation and click Next.
  5. Enter the following SQL query:

    SELECT
       *,
       CASE
          WHEN
             'Subtotal' < 1000 
          THEN
             "LOW" 
          WHEN
             'Subtotal' >= 1000 
             AND 'Subtotal' < 2000 
          THEN
             "MEDIUM" 
          ELSE
             "HIGH" 
       END
       AS 'Priority' 
    FROM
       T1

    CASE WHEN ... THEN ... ELSE ... END goes through conditions and return a value when the first condition is met.

    AS '...' outputs a new 'Priority' column.
  6. Click Next.
  7. Define the table settings and view options if needed. 
  8. Save the macro and the page.

If you want to replace the words Low, Medium and High by prominent statuses using the default Status macro or the Handy Status macro, just place a one-column table containing each status and the same column label in the macro body. Don't change anything in the SQL query.


Comparing two dates using If / Then construct

Use case:

You have a table with two columns: Target date and Completion date. You need to compare and rate these dates:

  • If the completion date is within 5 days, the status is ON TIME.
  • If the completion date is more than 5 days late, but less than 10 days, the status is LATE.
  • If the completion date is more than 10 days, the status is VERY LATE.

Solution:

  1. Switch the page to the edit mode.
  2. Insert the Table Transformer macro and paste the tables or the macros outputting tables within the macro body.
  3. Select the macro and click Edit.
  4. In the Presets tab select Custom transformation and click Next.
  5. Enter the following SQL query:

    SELECT *,
       CASE
          WHEN
             (('Completion date' - 'Target date') / "24h") < 5 
          THEN "ON TIME" 
          WHEN
             (('Completion date' - 'Target date') / "24h") > 5 
             AND 
             (('Completion date' - 'Target date') / "24h") <= 10 
          THEN "LATE" 
          ELSE "VERY LATE" 
       END
       AS 'Rating' 
    FROM T1
  6. Click Next.
  7. Define the date format.
  8. Save the macro and the page.

Using formulas

Use case:

You have a table with the number of added lines of code and the number of defects. You need to calculate the quality of the code. The number of defects less than one defect per 10 lines indicates the good quality of the code. Otherwise the quality of the code is bad.

Solution:

  1. Switch the page to the edit mode.
  2. Insert the Table Transformer macro and paste the tables or the macros outputting tables within the macro body.
  3. Select the macro and click Edit.
  4. In the Presets tab select Custom transformation and click Next.
  5. Enter the following SQL query:

    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

    ('...' * 10 / '...') AS '...' calculates the number of defects per 10 lines of code and outputs the 'Defects per 10 lines' column. 

    CASE WHEN ... THEN ... ELSE ... END AS '...' goes through conditions and return a value when the first condition is met and outputs the 'Code quality' column.
  6. Click Next.
  7. Define the table settings and view options if needed.
  8. Save the macro and the page.

You can merge two columns containing text with the help of formulas:

SELECT
   *,
   (
      'First name' + " " + 'Last name'
   )
   AS 'Full name' 
FROM
   T1

Merging tables using filtration

Use case:

You have two tables where the columns differ.

You need to merge two tables where the entrys meet the conditions:

  • transaction date in Table 1: October 2018
  • customer type in Table 2: business

Solution:

  1. Switch the page to the edit mode.
  2. Insert the Table Transformer macro and paste the tables or the macros outputting tables within the macro body.
  3. Select the macro and click Edit.
  4. In the Presets tab select Custom transformation and click Next.
  5. Enter the following SQL query:

    SELECT
       * 
    FROM
       T1 
       JOIN
          T2 
          ON T1.'Transaction ID' = T2.'Transaction ID' 
    WHERE
       (
          'Transaction Date' >= "10 / 1 / 2018" 
          AND 'Transaction Date' < "11 / 1 / 2018"
       )
       AND 
       (
          'Customer Type' = "Business"
       )

    WHERE ('...' >= "10/1/2018" AND '...' < "11/1/2018") AND ('...' = "...") extracts only those records that fulfill a specified condition.

  6. Click Next.
  7. Define the table settings and view options if needed.
  8. Save the macro and the page.

Calculating the number of workdays in a period of time

Use case:

You have a table containing a period of time (start date and end date).

You need to count the number of workdays during this time period.

Advanced use case:

You have an additional table with national holidays which you also shouldn't consider as workdays.

Solution:

  1. Switch the page to the edit mode.
  2. Insert the Table Transformer macro and paste the table within the macro body.
  3. Select the macro and click Edit.
  4. Switch to the SQL query tab.
  5. Enter the following SQL query:

    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 = 6 THEN 1
    	WHEN 'Start date'::Date->getDay() + 'Days' % 7 > 6 THEN 2
    	ELSE 0
    END AS 'Work days'
    FROM
    (SELECT *,
    (T1.'End date' - T1.'Start date') / "1d" + 1 AS 'Days'
    FROM T1)
  6. Click Next.
  7. Define the table settings and view options if needed.
  8. Save the macro and the page.


Solution for the advanced use case:

Enter the following SQL query:

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 = 6 THEN 1
	WHEN 'Start date'::Date->getDay() + 'Days' % 7 > 6 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 *, ('End date' - 'Start date') / "1d" + 1 AS 'Days' FROM T1) AS TT

Calculating cumulative (running) totals

Use case:

You have a table (a pivot table) containing a sequence of years and some numeric values.

You need to calculate cumulative (running) totals when the value of each next year is added to the sum of the previous ones.

Solution:

  1. Switch the page to the edit mode.
  2. Insert the Table Transformer macro and paste the table within the macro body.
  3. Select the macro and click Edit.
  4. Switch to the SQL query tab.
  5. Enter the following SQL query:

    SELECT 'Year', 
    SUM (TT2.'Requests') AS 'Requests'
    FROM T1 AS TT1
    JOIN T1 AS TT2 on TT1.'Year' >= TT2.'Year'
    GROUP BY TT1.'Year'
    ORDER BY TT1.'Year'
  6. Click Next.
  7. Define the table settings and view options if needed.
  8. Save the macro and the page.



Calculating standard deviation / variance

Use case:

You have a table (a macro outputting a table) containing a set of numeric values.

You need to calculate the standard deviation or variance.

Solution:

  1. Switch the page to the edit mode.
  2. Insert the Table Transformer macro and paste the table within the macro body.
  3. Select the macro and click Edit.
  4. Switch to the SQL query tab.
  5. Enter the following SQL query (select the functions depending on what you'd like to calculate):

    SELECT
    MEDIAN(T1.'Value') AS 'Median',
    STDDEV(T1.'Value') AS 'Standard deviation population',
    STDEV(T1.'Value') AS 'Standard deviation sample',
    VAR(T1.'Value') AS 'Variance sample',
    VARP(T1.'Value') AS 'Variance population'
    FROM T1
  6. Click Next.
  7. Define the table settings and view options if needed.
  8. Save the macro and the page.



Automatic filling blanks and updating values in tables

Use case:

You have two tables containing information about employees on different pages.

The first table is an Excel spreadsheet with data.

You need to fill in the blanks or update values in the second table with data from the first one by matching the 'Employee' columns.

Advanced use case:

You need to select values manually (without column matching) and add them into empty cells only.

Solution:

  1. Switch the page to the edit mode.
  2. Insert the Table Transformer macro and paste the tables or the macros outputting tables within the macro body.
  3. Select the macro and click Edit.
  4. In the Presets tab select Custom transformation and click Next.
  5. Enter the following SQL query: 

    SELECT T1.'Number', T1.'Employee', T1.'Position',
     CASE
      WHEN T2.'City' IS NULL 
      THEN T1.'Location'
      ELSE T2.'City'
     END
     AS 'Location'
    FROM T1 LEFT JOIN T2 ON T1.'Employee'->toLowerCase() = T2.'Employee'→toLowerCase()
  6. Click Next

  7. Define the table settings and view options if needed

  8. Save the macro and the page. 







Solution for the advanced use case:

Enter the following SQL query:

SELECT T1.'Number',T1.'Employee',T1.'Position',
 CASE
  WHEN T1.'Employee' = "John Anderson" AND T1.'Location' IS NULL
  THEN (SELECT T2.'City' FROM T2 WHERE T2.'Employee' = "John Anderson")
  WHEN T1.'Employee' = "Alban Jacobs" AND T1.'Location' IS NULL  
  THEN (SELECT T2.'City' FROM T2 WHERE T2.'Employee' = "Alban Jacobs")
  ELSE T1.'Location' 
 END
 AS 'Location'
FROM T1

Calculating the number of distinct (unique) values by categories

Use case:

You have a table (a macro outputting a table) containing multiple columns with text data.

You need to calculate the number of distinct (unique) values by categories. For example, the number of unique customers in every segment.

Solution:

  1. Switch the page to the edit mode.
  2. Insert the Table Transformer macro and paste the table within the macro body.
  3. Select the macro and click Edit.
  4. Switch to the SQL query tab.
  5. Enter the following SQL query (select the functions depending on what you'd like to calculate):

    SELECT T1.'Segment', 
    COUNT (DISTINCT (T1.'Customer Name')) AS 'Number of customers' 
    FROM T* 
    GROUP BY T1.'Segment'
  6. Click Next.
  7. Define the table settings and view options if needed.
  8. Save the macro and the page.



Number formatting

Use case:

You have a table or a macro outputting a table. 

You need to change the data format of the column containing numbers to add a dollar sign, a thousands separator into values and remove decimals.

Solution:

  1. Switch the page to the edit mode.
  2. Insert the Table Transformer macro and paste the table within the macro body.
  3. Select the macro and click Edit.
  4. Switch to the SQL query tab.
  5. Enter the following SQL query:

    SELECT 'Company',
    "$ "+FORMATNUMBER('Profit per month') as 'Profit'
    FROM T*
  6. Click Next.
  7. Define the table settings and view options
  8. Save the macro and the page.

FORMATNUMBER function allows you to format numbers according to the settings in the Table Transformer macro.

  • No labels