---
title: "Step 4a - Prepare Data for Reporting - Decodes"
slug: "step-4a-prepare-data-for-reporting-decodes"
updated: 2023-12-13T10:15:06Z
published: 2023-12-13T10:15:06Z
canonical: "docs.datajet.app/step-4a-prepare-data-for-reporting-decodes"
---

> ## 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 4a - Prepare Data for Reporting - Decodes

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 and reporting tables have been created.   See [Step 3 - Create Reporting Tables](/v1/docs/step-3-create-reporting-tables) if this has not yet been done.
- This step can be skipped if there is no requirement to create a dashboard that is filtered by products, or if the data already have a user-friendly format.

## Approach

When building a dashboard, or any report that is designed to show data to other users, presentation is important as it drives understanding. The data we are working with have product data stored as codes. This section shows how to decode those data into user-friendly categories and sub-categories.

This section details how to:

- **Decode** fields by mapping source values to new values

## Key Techniques

The following functional areas are introduced by this step:

- [Custom Decodes](/v1/docs/decode-field-custom) from Excel definition files

## Video Tutorial

[Video](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/BDS%20-%20Step%204a_0.mp4)

## Applying Decodes

The data in the Demo Database sales table contain some category data provided in the form of a code. To turn the data into a more human readable form we will "**decode**" them by mapping each source code to a new descriptive value. We are doing this so that later on, the final dashboard will be able to be filtered by specific categories and sub-categories.

What are decodes?Decoding Data is a way of taking a **source value** and applying a **label** to it that provides additional information about the meaning of the original value.  Often it is a case of taking a code and applying a more descriptive label (for example 0->Missing, 1->Present,  or A->Cat, B->Dog, C->Horse...).  This is the simplest form of decoding, where there is a **1 to 1 relationship** between the **code** (the source value) and the **decode** (the output value).

However, decodes are more powerful than simple 1 to 1 mappings of values - they can be used to group multiple source values into useful higher level categories in a **Many to 1** mapping.

For example a **1 to 1 mapping** might look as follows:

- 1->Tables
- 2->Chairs
- 3->Sheets
- 4->Duvet covers

A **many to 1 mapping** on the same data might look as follows:

- 1->Furniture
- 2->Furniture
- 3->Bedding
- 4->Bedding

### Decoding Sales Categories

We are going to begin by decoding the field [**sales.sub category code]**, which is loaded from the Demo Database as numeric codes.

The field will be decoded twice, using 2 different decode levels:

- Category (Many to 1 mapping)
- Sub Category (1 to 1 mapping)

The decode file that is provided with the demo database looks as follows:

![](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/image-1701341338125.png)

#### Category Decode - Many to 1 Mapping

To decode **sales.sub category code** to **sales.category,** do the following:

- **Right-click** on [**sales.sub category code**] and go to **Engineering | Decode | Custom**.  The Custom Decode dialog will appear
- **Name** the decode "Category"
- Click on the **Excel tab**
- Click on the **Workbook File Select icon** and select the file **Data Source/DJDemo2023/CategoryDecodes.xlsx**  
![](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/image-1701337510735.png)

Once the CategoryDecodes.xlsx file has been selected, select **Worksheet** "Sub Category" (if it is not selected automatically).

Now that the decode *file* has been assigned, we need to specify the *lookup columns* to use in the file. The **Code Column** and **Decode Column** drop downs will be populated with columns in worksheet "Sub Category"

- **Code Column**: This is the raw value, and should contain the values that are in DataJet in the column that is being decoded (**sales.sub category code**).  For this example, Code Column is column **A Code**
- **Decode Column:** This is the decoded value (the descriptive value).   For this example, Decode Column is column **C Category**

![](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/image-1701337101722.png)

![](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/image-1701096411128.png)

After completing these steps click the **Test** button to check that the Decode is correctly mapped:

![](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/image-1701096548703.png)

The categories: null, Technology, Office Supplies, and Furniture will appear with counts (as shown above). Here we can see that there are:

- 0 null records (i.e., records that are not mapped by the decode)
- 760,032 transactions of category "Technology"
- 2,779,560 records of category "Office Supplies"
- ...

Once the decode results have been verified, the new field can be created by selecting **Build.**

![](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/image-1701096718030.png)

Once the build completes, the new field (**category)** will appear in the **sales** table in project explorer.

![](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/image-1701096863685.png)

#### Decoding Sales Sub Categories (1 to 1 mapping)

For purposes of analysis, we will often use the higher level grouping of category, but sometimes we may require a greater level of detail, and for this a "sub category" mapping is required.

This time we will open the Custom Decode Dialog another way, go to **Engineering | Decode Field | Custom** and the Custom Decode Dialog will appear. **Drag and Drop** [**sales.****sub category code]** into the **Field** box and the Custom Decode Dialog will update.

![](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/image-1701097010334.png)

Assign the decode file by clicking on the **Excel** tab. Select a file to import by clicking the **file select icon.** Go into the **DJDemo2023** folder and select the **CategoryDecodes.xlsx** Excel file.

![](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/image-1701097174751.png)

When decoding "sub category code" to "category", we selected "C Category" as the decode column. To decode the field to the sub categories, we select "B Sub Category"

- **Code Column**: This is the raw value, and should contain the values that are in DataJet in the column that is being decoded (**sales.sub category code**).  For this example, Code Column is column **A Code**
- **Decode Column:** This is the decoded value (the descriptive value).   For this example, Decode Column is column **B** **Sub Category**

The **DATAPATH** should appear for the **CategoryDecodes** file. Again for the **Code Column** select the input as **A Code.** But this time for the **Decode Column** select the output as **B Sub Category**.

![](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/image-1701097397298.png)After completing these steps click the **Test** button to check that the Decode is running properly. ![](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/image-1701097501852.png)

You will see the sub categories appear with counts (as shown above). After verifying the mapping, select **Build** to create the field. ![](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/image-1701097627785.png)

A **Sub Category** field will appear in the **sales** table in project explorer.

![](https://cdn.document360.io/73b6469b-c1e4-47e9-aa65-986c8ebd17ca/Images/Documentation/image-1701097899584.png)