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
    SQL

    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.

You can use FORMATWIKI function to insert statuses into a table.