No Results
Joining Two Datasets with JavaScript & SQL

This page provides a working example of joining two data sets, Orders and Customers, on the customer ID using both JavaScript and SQL Transforms. You can download the orders CSV and customers CSV files and follow the steps outlined below.
The Customers dataset contains the basic customer information along with an id field which is used when they place an order.
The Orders dataset contains info about the orders placed. Every order has tracked the customer who purchased it by a customerId.

Joining Two Datasets with JavaScript Transform

Step 1: Create Two CSV Feeds

Take the following steps:

  1. Download orders.csv and customers.csv.
  2. Go to the Pipeline and click + Add Connection in the upper-left corner.
    A pop-up for selecting a connection type is displayed. 
  3. In the pop-up, select File / Server Filesystem connection type.
  4. Click Use Selected.
    A new page is displayed.
  5. In Connection Name, enter a name for the connection (for example, CustomerOrders).
  6. Make sure the Enable Connection toggle is on.
  7. In Server Path, provide a relative path from the installation folder of edgeCore. For the purpose of this example, you can leave the field as is.
  8. Click Next.
    You are taken to the Test Connection tab where the confirmation message Connection test was successful is displayed.
  9. Click Save and Close.
    The newly created connection is displayed in the Pipeline. Since we have two CSV files, we are going to create two CSV feeds now that you have established a connection to the filesystem.
  10. Hover over the CustomerOrders Server Filesystem connection for the gear icon to appear on the right, click the gear icon button in the connection box and select + .
    A pop-up for choosing a Feed Type is displayed.
  11. In the pop-up, select CSV.
    A new page is displayed.
  12. Create two CSV Feeds off of the previously created connection (one for Customers and one for Orders just like in the screenshot above).
    a) For both of these, in File Name upload the respective CSV files (customers.csv and orders.csv) and select them from the dropdown.
    b) In Feed Name, for the Customers CSV feed enter Customers, and for the Orders CSV enter Orders (by default, the name will be the same as the file name so you just need to remove CSV from the feed name, as the name will be important for our script).

    c) Click Next to go to CSV parser. No changes are needed here, so click Next again to go to Published Attributes. Here you can see all attributes from the csv files. No changes are needed here, so you can proceed to Data Preview where you can observe the data preview of your CSV feeds.
  13. Click Save and Close.
    The newly created feeds are displayed in the Pipeline (just like in the screenshot at the beginning of the article).

Step 2: Create JavaScript Transform

Now that you have created two CSV feeds, you can create a JavaScript transform that will join these two datasets.

  1. Hover over one of the created CSV feeds for the gear icon to appear on the right, click the gear icon button in the connection box and select + New Transform.

    A pop-up for choosing a Transform Type is displayed.
  2. In the pop-up, select JavaScript.
    A new page is displayed.
  3. In Configure, provide a Transform Name (for example, Customer_Orders) and click Next to go to Script References.
    No changes are needed here so click Next to go to Dataset References.
  4. In Dataset References, only one dataset is displayed (the one from which you created the transform) so click Add Dataset and find the other dataset in the dropdown as both Customers and Orders need to be referenced.

    Click Next to go to Script.
  5. In Script, delete the default script, download this js file and paste its content. 
  6. Click Next to go to Published Attributes. Here you can see all attributes from the csv files. No changes are needed here.
  7. Click Next to proceed to Data Preview where you should see the end result — two datasets being joined.
  8. Click Save and Close.
    The newly created transform is displayed in the Pipeline.

 

Joining Two Datasets with SQL Transform

In this example, we are going to join the Orders table with the Customers table using customerId. In addition, we are going to use an INNER JOIN, so it includes only customers who have placed at least one order. This is useful for building dashboards, reports, or exports where you want a complete picture of each order along with customer demographics.

Step 1: Create Two CSV Feeds

Take the following steps:

  1. Download orders.csv and customers.csv.
  2. Go to the Pipeline and click + Add Connection in the upper-left corner.
    A pop-up for selecting a connection type is displayed. 
  3. In the pop-up, select File / Server Filesystem connection type.
  4. Click Use Selected.
    A new page is displayed.
  5. In Connection Name, enter a name for the connection (for example, CustomerOrders).
  6. Make sure the Enable Connection toggle is on.
  7. In Server Path, provide a relative path from the installation folder of edgeCore. For the purpose of this example, you can leave the field as is.
  8. Click Next.
    You are taken to the Test Connection tab where the confirmation message Connection test was successful is displayed.
  9. Click Save and Close.
    The newly created connection is displayed in the Pipeline. Since we have two CSV files, we are going to create two CSV feeds now that you have established a connection to the filesystem.
  10. Hover over the CustomerOrders Server Filesystem connection for the gear icon to appear on the right, click the gear icon button in the connection box and select + .
    A pop-up for choosing a Feed Type is displayed.
  11. In the pop-up, select CSV.
    A new page is displayed.
  12. Create two CSV Feeds off of the previously created connection (one for Customers and one for Orders just like in the screenshot above).
    a) For both of these, in File Name upload the respective CSV files (customers.csv and orders.csv) and select them from the dropdown.
    b) In Feed Name, for the Customers CSV feed enter Customers, and for the Orders CSV enter Orders (by default, the name will be the same as the file name so you just need to remove CSV from the feed name).

    c) Click Next to go to CSV parser. No changes are needed here, so click Next again to go to Published Attributes. Here you can see all attributes from the csv files. No changes are needed here, so you can proceed to Data Preview where you can observe the data preview of your CSV feeds.
  13. Click Save and Close.
    The newly created feeds are displayed in the Pipeline (just like in the screenshot at the beginning of the example).

 

Step 2: Create SQL Transform

Now that you have created two CSV feeds, you can create a SQL transform that will join these two datasets.

  1. Hover over one of the created CSV feeds for the gear icon to appear on the right, click the gear icon button in the connection box and select + New Transform.

    A pop-up for choosing a Transform Type is displayed.
  2. In the pop-up, select SQL Transform.
    A new page is displayed.
  3. In Configure, provide a Transform Name (for example, JoinByCustomerID) and click Next to go to Dataset References.
  4. In Dataset References, only one dataset is displayed (the one from which you created the transform) so click Add Dataset and find the other dataset in the dropdown as both Customers and Orders need to be referenced.

    Click Next to go to Query.
  5. In Query, delete the default query and paste the following:
    SELECT
      o.orderDate,
      o.product,
      o.price,
      o.customerId,
      c.id AS customerId,
      c.firstName,
      c.lastName,
      c.birthday,
      c.city,
      c.state,
      c.country
    FROM
      {src.Orders} o
    INNER JOIN
      {src.Customers} c
    ON
      o.customerId = c.id;

    Query information:
     Retrieves:
      – Order Details: orderDate, product, price, and customerId.
      – Customer Details: id, firstName, lastName, birthday, city, state, country.
    Aliased Tables:
     – o for Orders
     – c for Customers
    Aliased Column:
     – c.id AS customerId ensures you still get the customer ID from the customer table without confusion (since both tables have a customer ID field)
  6. Click Next to go to Published Attributes. Here you can see all attributes from the csv files. No changes are needed here.
  7. Click Next to proceed to Data Preview where you should see the end result — two datasets being joined.
  8. Click Save and Close.
    The newly created transform is displayed in the Pipeline.

Terms | Privacy