# Analytic Query

## ![](/files/LkgM7pLIjCUkdCCQvAcg) Analytic Query

### Description <a href="#description" id="description"></a>

The Analytic Query transform allows you to peek forward and backwards across rows in a pipeline.

Examples of common use cases are:

* Calculate the "time between orders" by ordering rows by order date, and LAGing 1 row back to get previous order time.
* Calculate the "duration" of a web page view by LEADing 1 row ahead and determining how many seconds the user was on this page.

### Supported Engines <a href="#supported_engines" id="supported_engines"></a>

| Hop Engine | <sup>✓</sup> |
| ---------- | ------------ |
| Spark      | X            |
| Flink      | X            |
| Dataflow   | X            |

### Options

| Option                   | Description                                                                                                                                                                                                                                                                             |
| ------------------------ | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Transform name           | The name of this transform as it appears in the pipeline workspace.                                                                                                                                                                                                                     |
| Group fields table       | Specify the fields you want to group. Click Get Fields to add all fields from the input stream(s). The transform will do no additional sorting, so in addition to the grouping identified (for example CUSTOMER\_ID) here you must also have the data sorted (for example ORDER\_DATE). |
| Analytic Functions table | Specify the analytic functions to be solved.                                                                                                                                                                                                                                            |
| New Field Name           | the name you want this new field to be named on the stream (for example PREV\_ORDER\_DATE)                                                                                                                                                                                              |
| Subject                  | The existing field to grab (for example ORDER\_DATE)                                                                                                                                                                                                                                    |
| Type                     | <p>Set the type of analytic function:</p><ul><li>Lead - Go forward N rows and get the value of Subject</li><li>Lag - Go backward N rows and get the value of Subject</li></ul>                                                                                                          |
| N                        | The number of rows to offset (backwards or forwards)                                                                                                                                                                                                                                    |

### Group field examples

While it is not mandatory to specify a group, it can be useful for certain cases. If you create a group (made up of one or more fields), then the "lead forward / lag backward" operations are made only within each group. For example, suppose you have this:

```highlight
X   , Y
--------
aaa , 1
aaa , 2
aaa , 3
bbb , 4
bbb , 5
bbb , 6
```

And you want to create a field named Z, with the Y value in the previous row.

If you only care about the Y field, you don’t need to group. And you will have the following result:

```highlight
X   , Y , Z
------------
aaa , 1 , <null>
aaa , 2 , 1
aaa , 3 , 2
bbb , 4 , 3
bbb , 5 , 4
bbb , 6 , 5
```

But if you don’t want to mix the values for aaa and bbb, you can group by the X field, and you will have this:

```highlight
X   , Y , Z
------------
aaa , 1 , <null>
aaa , 2 , 1
aaa , 3 , 2
bbb , 4 , <null>
bbb , 5 , 4
bbb , 6 , 5
```

Thus, by grouping (provided the input is sorted according to your grouping), you can be assured that lead or lag operations will not return row values outside of the defined group.


---

# Agent Instructions: 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:

```
GET https://docs.primeur.com/data-shaper-1.21/knowing-the-data-shaper-designer/pipelines/transforms/analyticquery.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
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.