> For the complete documentation index, see [llms.txt](https://docs.redivis.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.redivis.com/reference/workflows/transforms/step-create-variables.md).

# Step: Create variables

## Overview

A **Create variables** block uses methods to make new variables based on existing data. The method selected will dictate how the block operates.

#### Example starting data:

```
/*---------+--------*
 | student | score  |
 +---------+--------+
 | jane    | 83     |
 | neal    | 35     |
 | sam     | 74     |
 | pat     | 62     |
 *---------+--------*/
```

#### Example output data:

Creating a new variable for the letter grade each student got on their test.

```
/*---------+--------+-------*
 | student | score  | grade |
 +---------+--------+-------+
 | jane    | 83     | B     |
 | neal    | 35     | F     |
 | sam     | 74     | C     |
 | pat     | 62     | D     |
 *---------+--------+-------*/
```

## Step structure

<div data-with-frame="true"><figure><img src="https://1672950126-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LVodLwUXgJUGcm5Cvso%2Fuploads%2FNJA6RWWBS43ybjV8CVcF%2FScreenshot%202023-05-04%20at%2010.33.28%20AM.png?alt=media&amp;token=f0983561-7caf-4a6a-9013-e517a2d8b42c" alt=""><figcaption></figcaption></figure></div>

* There will be at least one new variable block where you define and complete a new variable method.
* When multiple blocks exist, the variables are created in sequence and can reference each other.

## Field definitions

<table><thead><tr><th width="238">Field</th><th>Definition</th></tr></thead><tbody><tr><td><strong>Name</strong></td><td>The name of the variable being created. This must follow all naming standards</td></tr><tr><td><strong>Method</strong></td><td>The way that the new variable will be created. Choosing this will bring up additional fields to complete specific to the chosen method. <a href="/reference/workflows/transforms/variable-creation-methods.md">See all methods here</a>.</td></tr></tbody></table>

{% hint style="warning" %}
Some methods are only available for certain variable types, so you might need to [retype](/reference/workflows/transforms/step-retype.md) variables before you can use them in the method you've chosen.
{% endhint %}

## Analytic methods

Analytic methods are a special category of method that allow for each row to be computed individually. When using an analytic new variable method, new tools become available:

* A **partition** segments data based on values in the selected partition variables and computes the analytic method within those segments separately.
* A **window** defines which records are used to compute the analytic method. Usually this is accompanied with an order clause.

Examples 3 and 4 below go into more detail for analytic methods.

## Examples

### Example 1: Date extract

A simple new variable format is extracting one part of a variable to make a new variable. In our data, we have a full date including year, month, and day, but we want to extract the year for use elsewhere.

#### Starting data:

```
/*---------+-------+---------+------------*
 | test    | score | student | date       |
 +---------+-------+---------+------------+
 | quiz    | 83    | jane    | 2020-04-01 |
 | quiz    | 35    | pat     | 2020-04-01 |
 | quiz    | 89    | sam     | 2020-04-01 |
 | midterm | 74    | jane    | 2020-05-01 |
 | midterm | 62    | pat     | 2020-05-01 |
 | midterm | 93    | sam     | 2020-05-01 |
 | final   | 77    | jane    | 2020-06-01 |
 | final   | 59    | pat     | 2020-06-01 |
 | final   | 92    | sam     | 2020-06-01 |
 *---------+-------+---------+------------*/
```

#### Input fields:

<div data-with-frame="true"><figure><img src="https://1672950126-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LVodLwUXgJUGcm5Cvso%2Fuploads%2F3Dh9hhThLBP5QCq1pGar%2FScreenshot%202023-05-04%20at%2010.37.26%20AM.png?alt=media&amp;token=c186fc48-55b8-49de-b45f-e6b2871d42c2" alt=""><figcaption></figcaption></figure></div>

* **Name:** We name our new variable `test_month`.
* **Method:** Once we chose `Date extract` as our method, new fields appear
  * **Variable to extract:** The variable with the data we want to extract from. In our example, `date`.
  * **Date part:** The part of the variable we want to extract. Since `date` is a date type variable, the information about date is stored in the correct format and can be easily extracted. We choose `Month`, since that is what we want to create.
* More methods to choose from can be found in the [Variable creation methods](/reference/workflows/transforms/variable-creation-methods.md).

#### Output data:

```
/*---------+-------+---------+------------+--------------*
 | test    | score | student | date       | test_month   |
 +---------+-------+---------+------------+--------------+
 | quiz    | 83    | jane    | 2020-04-01 | April        |
 | quiz    | 35    | pat     | 2020-04-01 | April        |
 | quiz    | 89    | sam     | 2020-04-01 | April        |
 | midterm | 74    | jane    | 2020-05-01 | May          |
 | midterm | 62    | pat     | 2020-05-01 | May          |
 | midterm | 93    | sam     | 2020-05-01 | May          |
 | final   | 77    | jane    | 2020-06-01 | June         |
 | final   | 59    | pat     | 2020-06-01 | June         |
 | final   | 92    | sam     | 2020-06-01 | June         |
 *---------+-------+---------+------------+--------------*/
```

### Example 2: Case method

The `Case` method (if/else) allows us to specify one or more conditions to create the values of the new variable.

For example, we can create a variable capturing the grade of each test in our data.

#### Starting data:

```
/*---------+-------+---------+------------*
 | test    | score | student | date       |
 +---------+-------+---------+------------+
 | quiz    | 83    | jane    | 2020-04-01 |
 | quiz    | 35    | pat     | 2020-04-01 |
 | quiz    | 89    | sam     | 2020-04-01 |
 | midterm | 74    | jane    | 2020-05-01 |
 | midterm | 62    | pat     | 2020-05-01 |
 | midterm | 93    | sam     | 2020-05-01 |
 | final   | 77    | jane    | 2020-06-01 |
 | final   | 59    | pat     | 2020-06-01 |
 | final   | 92    | sam     | 2020-06-01 |
 *---------+-------+---------+------------*/
```

#### Input fields:

<div data-with-frame="true"><figure><img src="https://1672950126-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LVodLwUXgJUGcm5Cvso%2Fuploads%2FD1oYD4kcFLOges8Acx1x%2FScreenshot%202023-05-04%20at%2010.40.48%20AM.png?alt=media&amp;token=e88b8ffc-6541-4679-b281-83a454221ea3" alt=""><figcaption></figcaption></figure></div>

* When run, each row of the table is evaluated in each section of the case statement (`If`, `Else if`, `Else set to`) until it matches something and is set to the corresponding value.
  * After the first section where a row meets the criteria, the new variable value will be set and no other sections will be evaluated.
* Comparison statements used here will operate the same as they do in the [Filter](/reference/workflows/transforms/step-stack.md) step and can be nested in the same way.

#### Output data:

```
/*---------+-------+---------+------------+----------------*
 | test    | score | student | date       | letter_grade   |
 +---------+-------+---------+------------+----------------+
 | quiz    | 83    | jane    | 2020-04-01 | B              |
 | quiz    | 35    | pat     | 2020-04-01 | F              |
 | quiz    | 89    | sam     | 2020-04-01 | B              |
 | midterm | 74    | jane    | 2020-05-01 | C              |
 | midterm | 62    | pat     | 2020-05-01 | F              |
 | midterm | 93    | sam     | 2020-05-01 | A              |
 | final   | 77    | jane    | 2020-06-01 | C              |
 | final   | 59    | pat     | 2020-06-01 | F              |
 | final   | 92    | sam     | 2020-06-01 | A              |
 *---------+-------+---------+------------+----------------*/
```

### Example 3: Partitioned analytic methods

We can use an analytic method to compute a value for each row, rather than the entire table. By using a **partition** we can define groups of rows to calculate across.

For example, in our grades data we can calculate the average score of each test.

#### Starting data:

```
/*---------+-------+---------+------------*
 | test    | score | student | date       |
 +---------+-------+---------+------------+
 | quiz    | 83    | jane    | 2020-04-01 |
 | quiz    | 35    | pat     | 2020-04-01 |
 | quiz    | 89    | sam     | 2020-04-01 |
 | midterm | 74    | jane    | 2020-05-01 |
 | midterm | 62    | pat     | 2020-05-01 |
 | midterm | 93    | sam     | 2020-05-01 |
 | final   | 77    | jane    | 2020-06-01 |
 | final   | 59    | pat     | 2020-06-01 |
 | final   | 92    | sam     | 2020-06-01 |
 *---------+-------+---------+------------*/
```

#### Input fields:

<div data-with-frame="true"><figure><img src="https://1672950126-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LVodLwUXgJUGcm5Cvso%2Fuploads%2FQcfkRF6Ubds4Jl4RPM0i%2FScreenshot%202023-05-04%20at%2010.44.04%20AM.png?alt=media&amp;token=db3c64cd-5d9b-4930-8cfb-0b9354b6b52d" alt=""><figcaption></figcaption></figure></div>

* **Method:** We want to calculate the average, so we select `Average`.
* **Partition**: This is where we define the groups in which the average will be calculated.
  * If one or more variables are selected here, the average for the new `score_average` variable will be computed across all rows that are the same in the selected variable(s).
  * If no variables are entered here, then the average will be computed across the entire table.
  * We want to average scores from the same test, so we select `test` here.
* **Variable to aggregate**: This variable contains the data we want to average.
* **Window**: We don't want to define a window in this average so we leave it on `All rows in the partition`.

#### Output data:

```
/*---------+-------+---------+------------+---------------*
 | test    | score | student | date       | score_average |
 +---------+-------+---------+------------+---------------+
 | quiz    | 83    | jane    | 2020-04-01 | 69            |
 | quiz    | 35    | pat     | 2020-04-01 | 69            |
 | quiz    | 89    | sam     | 2020-04-01 | 69            |
 | midterm | 74    | jane    | 2020-05-01 | 76.3333       |
 | midterm | 62    | pat     | 2020-05-01 | 76.3333       |
 | midterm | 93    | sam     | 2020-05-01 | 76.3333       |
 | final   | 77    | jane    | 2020-06-01 | 76            |
 | final   | 59    | pat     | 2020-06-01 | 76            |
 | final   | 92    | sam     | 2020-06-01 | 76            |
 *---------+-------+---------+------------+---------------*/
```

To create the new variable, the values in `score` are averaged together for each row with the same value in `test`. For example, there are three rows with the value `quiz` in `test`, which average to 69, so all rows with `quiz` show the same average of 69 in the new variable.

### Example 4: Windowed analytic methods

We can use a window to calculate a moving average. A window will define how many rows before or after the current row to use when calculating the average.

#### Example data:

```
/*-------+----------+------------*
 | score | student  | date       |
 +-------+----------+------------+
 | 10    | quiz1    | 2020-01-01 |
 | 10    | quiz1    | 2020-02-01 |
 | 40    | quiz1    | 2020-03-01 |
 | 30    | quiz2    | 2020-04-01 |
 *-------+---------+------------*/
```

#### Input fields:

<div data-with-frame="true"><figure><img src="https://1672950126-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LVodLwUXgJUGcm5Cvso%2Fuploads%2FIJXGInMvm5Kqgyns1OB2%2FScreenshot%202023-05-04%20at%2010.46.14%20AM.png?alt=media&amp;token=e4d4fced-e6e1-4c2c-a12a-7e007987c00c" alt=""><figcaption></figcaption></figure></div>

* **Partition:** We choose not to use a partition in this example since our data does not need to be segmented.
* **Variable to aggregate:** The numbers we want to average are in the `score` variable so we choose that here.
* **Window:** We want to create a moving average based on one entry before and after the current row, so we select `Rows`.
* **Order by:** Our data is already ordered by date in this table, but if it wasn't we would definitely need to order on the relevant variable here.
* **Rows preceding / Rows following:** This is where we define how many rows to include in the average.

#### Output data:

```
/*-------+---------+------------*---------------+
 | score | test    | date       | score_average |
 +-------+---------+------------+---------------+
 | 10    | quiz1   | 2020-01-01 | 10            |
 | 10    | quiz1   | 2020-02-01 | 20            |
 | 40    | quiz1   | 2020-03-01 | 26.6667       |
 | 30    | quiz2   | 2020-04-01 | 35            |
 *-------+---------+------------+---------------*/
```

For each row, one row preceding and following is used to compute the `score_average`. So for the first row we average 10 and 10 (since no preceding rows exist, it is excluded). For the second row 10, 10, and 40 are averaged. This process repeats until the end of the table is reached.

Note that we could also use Range instead of Rows for our window, if our question was time based. In other words, the average score over 1 month preceding and 1 month following.

### Example 5: Windowed & partitioned analytic methods

Continuing the previous example: if a partition were included, this same process would be completed separately for the values in each partition. If `test` were used as the partition here, the outcome would look different.

#### Output data:

```
/*-------+---------+------------*---------------+
 | score | test    | date       | score_average |
 +-------+---------+------------+---------------+
 | 10    | quiz1   | 2020-01-01 | 10            |
 | 10    | quiz1   | 2020-02-01 | 20            |
 | 40    | quiz1   | 2020-03-01 | 25            |
 | 30    | quiz2   | 2020-04-01 | 30            |
 *-------+---------+------------+---------------*/
```

Since `quiz2` is in a separate partition, those rows are averaged separately.

###


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://docs.redivis.com/reference/workflows/transforms/step-create-variables.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.