Page History

Panel

borderStyle	solid
title	On this page you will learn about:

Table of Contents

maxLevel	2
minLevel	2

Panel

borderStyle	solid
title	Read more:

Default automatic presets

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:

Switch the page to the edit mode.
Insert the Table Transformer macro and paste the table or the macros outputting tables within the macro body.
Select the macro and click Edit.
In the Presets tab select Custom transformation and click Next.

Enter the following SQL query:

Code Block

language	sql

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.

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

Handy Carousel

delay	10

Info
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.

Handy Carousel

delay	10

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:

Switch the page to the edit mode.
Insert the Table Transformer macro and paste the tables or the macros outputting tables within the macro body.
Select the macro and click Edit.
In the Presets tab select Custom transformation and click Next.

Enter the following SQL query:

Code Block

language	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

('...' * 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.

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

Handy Carousel

delay	10

Info

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

Code Block

language	sql

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

Handy Carousel

delay	10

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:

Switch the page to the edit mode.
Insert the Table Transformer macro and paste the tables or the macros outputting tables within the macro body.
Select the macro and click Edit.
In the Presets tab select Custom transformation and click Next.

Enter the following SQL query:

Code Block

language	sql

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.

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

Handy Carousel

delay	10

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:

Switch the page to the edit mode.
Insert the Table Transformer macro and paste the table within the macro body.
Select the macro and click Edit.
Switch to the SQL query tab.

Enter the following SQL query:

Code Block

language	sql

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

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

Handy Carousel

delay	10

Solution for the advanced use case:

Enter the following SQL query:

Code Block

language	sql

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

Handy Carousel

delay	10

Space shortcuts

Page tree

Versions Compared

Old Version 9

New Version 10

Key

Using If / Then construct

Using formulas

Merging tables using filtration

Calculating the number of workdays in a period of time