Merging tables by partial match

Use case:

You need to look up tables by partial match.

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 *
FROM T1 LEFT JOIN T2 ON 
T1.'Codes'->split("KR")->indexOf(REPLACE(T2.'Code', "KR", ""))>-1
    SQL

  6. Click Next. 

  7. Save the macro and the page.

Merging tables using SQL 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"
   )
    SQL

    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.

You can use FORMATWIKI function for the purposes of cell formatting.

Automatic filling blanks and updating values in tables

Use case:

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

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()
    SQL

  6. Click Next

  7. Define the table settings and view options   if needed

  8. Save the macro and the page. 

You can use FORMATWIKI function for the purposes of cell formatting.

Advanced use case:

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

Solution:

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
SQL