--- title: "Step 3 - Create Reporting Tables" slug: "step-3-create-reporting-tables" updated: 2023-12-23T11:07:39Z published: 2023-12-23T11:07:39Z canonical: "docs.datajet.app/step-3-create-reporting-tables" --- > ## Documentation Index > Fetch the complete documentation index at: https://docs.datajet.app/llms.txt > Use this file to discover all available pages before exploring further. # Step 3 - Create Reporting Tables NOTEThis article is part of the "[Tutorial - Basic Sales Dashboard](/v1/docs/basic-sales-dashboard-tutorial-overview)" series. It assumes the following: - The user is logged in to an active DataJet realm that contains the DJDemo2023 data (or data arranged in a [similar structure](/v1/docs/djdemo2023-demo-database-schema)) - Demo Data (or data in a similar structure) has been loaded into a project - see [Step 2 - Create Project and Load Data](/v1/docs/step2-create-project-and-load-data) if this has not yet been done. - If working on an existing data structure that matches the DJDemo2023 schema, you can skip this step and go to [Step 4 - Prepare Data for Reporting](/v1/docs/step-4a-prepare-data-for-reporting-decodes) ## Approach Once the transactions table is loaded, customer data is loaded and the two tables joined. The sales data is augmented with product information. This article shows how to: - **Import** a delimited file - **Join** tables - **Transfer/Copy** data from one joined table to another - **Delete** a temporary table - **Create a key table** from another table ## Key Techniques The following functional areas are introduced by this step: - [Script Editor](/v1/docs/script-editor) - [Load File Dialog](/v1/docs/delimited-file) - [File Selection Dialog](/v1/docs/delimited-file) - [Joins Dialog](/v1/docs/joins) - [Copy Down](/v1/docs/copy-down-p-f) - [Delete Table](/v1/docs/scriptingmethod-1) - [Make Key Table Dialog](/v1/docs/make-key-table) ## Video Tutorial [Video](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/BDS%20-%20Step%203_0.mp4) ## Importing Data Using the Script Editor #### Import customer data So far in this series we have loaded **sales** (or transaction) data from a delimited file. The next step is to import the associated **customer** data. To do this we will use the script editor - import file method. Go to **Script Editor | Import Data | Delimited File****![](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/image-1700932752873.png)** Select a file to import by clicking the **file select icon** (on the right-hand side of the page) to bring up the file selection dialog.![](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/image-1679563198850.png) Select **DJ****Demo2023/customer.dat** file to load and select **Open/Select.![](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/image-1700933149400.png)** NoteIf the "Guess Data types for SKIPPED fields?" message does not appear, select **Tools | Auto-guess Data types** from the importer dialog menu ![](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/image-1700933381956.png) After confirming data types are specified for all fields in the file, select **Save** from the Delimited File Importer dialog![](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/image-1700933501623.png) Click ****Run.**** The data will load, and the **customer** data will be visible in the Database Explorer ![](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/image-1700933870031.png) #### Import tproduct data The **t****product** data also needs to be imported. To do this we will use script editor again: - Go to **Script Editor | Import Data | Delimited File** - Select a file to import by clicking the **file select icon** (on the right-hand side of the page) to bring up the file selection dialog. - Select **DJ****Demo2023/product.dat** file to load and select **Open/Select.![](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/image-1700933266523.png)** - The "Guess Data Types for SKIPPED fields?"  message may not appear, in which case select **Tools | Auto-guess Data types** from the importer dialog menu. - If potential continuous fields are detected, the "Identify Key Fields" dialog will be displayed - use the option button to help select fields which should be loaded as continuous.  ![](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/image-1700935228807.png) - **Name** the table "tproduct" - (this is a temporary table, hence prefixing the table name with t) - Then select **Save** from the Delimited File Importer dialog. - Click ****Run.**** The data will load, and the **t****product** data will be visible in the Database Explorer.![](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/image-1700933967191.png) ## Joining Tables Once the **sales**, **customer** and **t****product** tables have been loaded, the next step is to join the tables. We will begin by joining the customer table to the sales table. #### Available Methods The following methods are available for joining data: 1. '**Drag and Drop**'. Join a table by dragging the **primary key** in the **primary** table and dropping it onto the **foreign key** in the **foreign** table 2. **Join Dialog**.  Drag and drop primary key and foreign key from the Database Explorer onto the **Engineering | Joins** dialog 3. **AddJoin Method**. Select **Script Editor | Methods | Data Model | AddJoin** and add the **AddJoin** method to a script What is the difference between a primary and foreign table? The primary table contains the unique identifier, known as the **primary ke****y**, for each record. When creating joins, 1 record for each unique value of the primary key will be included in the join. Often a primary key is fully unique (i.e., every record has a different value for the primary key) The foreign table contains the **foreign key** that links records in the foreign table back to the primary key in the primary table, establishing a relationship between records in the 2 tables. The foreign key contains the same data as the primary key, but may have **repeated values**. A direct ONE TO MANY join is a join that goes directly from a Primary Table (ONE side of a join) to a Foreign Table (MANY side of a join). ![](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/image-1682425635477.png) For example, if the primary table is **customers**, and the foreign table is **sales**, then each customer in the primary table will be linked to one or more transactions in the sales table: ![](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/image-1701264138835.png) #### Joining Customer and Sales table In our example **customer id** is the **primary key** on the primary table (**customer** table) and **customer** **id** is also the **foreign key** on the foreign table (**sales** table). So we will drag and drop the **customer.****customer id onto** **sales.customer id******.**** ****![](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/image-1701196658591.png)**** Case Sensitive Field NamesNote that field names are **case sensitive** - "Cust ID" is not the same as "cust id" or "Cust id" If it's valid for the **customer ids** to join (i .e. if the tables are not already linked and if the data types match), then an option to join the two tables will come up and it can be selected.![](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/image-1700934357131.png) TipIf the Join option doesn't show, check that the [data-types](/v1/docs/getting-started-data-types) of the two fields match and that the table is not already joined. Consider using the [Engineering | Joins](/v1/docs/joins) dialog to create the join, as this will return an error message explaining why the join cannot be built. #### Joining Sales and tProduct tables Now we are going to create a join between the Sales and tProduct tables. In this instance the **t****product table** is the **primary table** and the **sales table** is the **foreign table**. This time we will use **Engineering | Joins.** ![](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/image-1701196777193.png) Open **Engineering | Joins.** The **customer > sales** join will be visible: ![](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/image-1700935360734.png) To create a new join between the **Product** and **Sales** tables: 1. Drag and drop the t**product.transaction id** from the tproduct table into the **Primary Field** box. 2. Drag and drop the **sales.transaction id** from the sales table and drop it into the **Foreign Field** box 3. Select **Add** 4. The new join will appear in the list of Direct Joins: **![](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/image-1700935721007.png)** ## Copying Data from the Product Table to the Sales Table Now that the tproduct and sales tables are joined, we can copy product information from the temporary tproduct table to the sales table. This will be required for later analysis. Copy DownIn this instance we will copy **product id** from the **tproduct** table to the **sales** table. Because this is copying data from the **primary** table to the **foreign** table, this is a **"Copy Down"** operation Drag **tproduct****.****product id** from the **tproduct** table and drop it on to the **sales** table and select **Copy Down.** The job window will appear with COPYDOWN as a task. Once this is complete (on the demo database this should complete almost instantly), **product id** will appear in the **sales** table. ![](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/image-1700915776533.png) ## Create Product Table from Sales Table Using Key Table #### Delete temporary tproduct Table We no longer need the **tproduct** table as we have extracted the information we need from it. To delete it right click on **t****product** and select **Delete**. ![](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/image-1700932011593.png) #### Creating a New Product Table From the Sales Table We are now going to create a table of unique product ids from the sales table. This is known as "Creating a Key Table" and is a common action when preparing data for reporting or engineering. Create Key Table - Create Key Table takes a field from a *source table,* extracts all the unique values for that field, and *creates a new table with a key field of the unique values*. In the example here, the Key Field is **product id.** - In the DjDemo2023 database there are 4.3m million records on the Sales table, and in those 4.3 million records, there are **653 unique product ids**. - Create Key Table will create a new table (in this case called product) with a key field called "product id".  The product table will have **653 records** in it - one for each unique product id in the source table. To create a product table from a sales table right click on ****sales.product id**** hover over **Advanced** and select **Make Key Table.** ![](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/image-1700931089913.png) Make Key Table not available?If Make Key Table is not available on the Advanced Menu, it can be accessed from **Engineering | Make Key Table** The Make Key Table dialog will appear. Unselect **Auto Engineering** as this is not necessary for our purposes. However leave **Auto Join** selected. ![](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/image-1700931452082.png) Select **Build** and a new product table will appear in the Project Explorer. This will be a table containing one record for each unique product id in the sales table. ![](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/image-1700932313643.png)