Alteryx DataPrep

https://help.alteryx.com/dataprep/en/
Admin
Alteryx DataPrep is a data preparation and cleaning tool that helps users transform and prepare data...
Tokens:1,432,515
Snippets:21,207
Trust Score:10
Update:8 months ago
Show doc for...
Tokens:
Raw
### RIGHTFIND Function Basic Usage Examples

Source: https://help.alteryx.com/dataprep/en/trifacta-application/wrangle-language/string-functions/rightfind-function

Demonstrates the basic usage of the RIGHTFIND function with column references, string literals, and conditional logic. It shows how to search for substrings, control case sensitivity, and specify starting positions.

```Wrangle
rightfind(MyName,'find this',true,0)
rightfind('Hello, World','lo',false,2)
if(rightfind(SearchPool,'FindIt') >= 0, 'found it', '')
```

--------------------------------

### Wrangle Pattern Matching for Two Digits at Start

Source: https://help.alteryx.com/dataprep/en/trifacta-application/reference/ui-reference/transformer-page/context-panel/transform-builder

This example uses Wrangle's macro-like pattern identifiers to match values that start with exactly two digits. It leverages {start} and {digit} to define the pattern.

```Wrangle
{start}{digit}{digit}
```

--------------------------------

### MAXIF Function Syntax and Arguments Example (Wrangle)

Source: https://help.alteryx.com/dataprep/en/trifacta-application/wrangle-language/aggregate-functions/maxif-function

Illustrates the general syntax for the MAXIF function in Wrangle, including the required column reference and test expression, as well as optional group and limit parameters.

```Wrangle
maxif(col_ref, test_expression) [group:group_col_ref] [limit:limit_count]
```

--------------------------------

### REST API Connection - Single GET Method

Source: https://help.alteryx.com/dataprep/en/platform/connections/connection-types/rest-api-connections

Example of creating a REST API connection that uses query parameters for authentication and enables a single GET endpoint for 'table1'.

```APIDOC
## POST /api/connections

### Description
Creates a REST API connection using query parameters for authentication and defining a single GET endpoint.

### Method
POST

### Endpoint
/api/connections

### Parameters
#### Request Body
- **vendor** (string) - Required - The vendor type, 'jdbc_rest'.
- **vendorName** (string) - Required - The vendor name, 'jdbc_rest'.
- **name** (string) - Required - The name of the connection.
- **description** (string) - Optional - A description for the connection.
- **type** (string) - Required - The connection type, 'jdbc'.
- **params** (object) - Required - Connection parameters.
  - **base_URI** (string) - Required - The base URI for the API.
  - **connectStrOpts** (string) - Optional - Additional connection string options.
- **credentialType** (string) - Required - The type of credential authentication, 'httpQueryBasedAuth'.
- **credentials** (array) - Required - An array of credential key-value pairs.
  - **queryKey** (string) - Required - The name of the query parameter for authentication.
  - **queryValue** (string) - Required - The value of the authentication query parameter.
- **endpoints** (array) - Required - An array of endpoint configurations.
  - **tableName** (string) - Required - The name of the table to represent the endpoint data.
  - **httpMethod** (string) - Required - The HTTP method for the endpoint, 'get'.
  - **endpoint** (string) - Required - The specific endpoint path.
  - **requestBody** (string) - Optional - The request body for the endpoint (not typically used for GET).
  - **xPath** (string) - Optional - XPath expression to extract data from the response.
  - **dataModel** (string) - Required - The data model type, 'document'.
  - **headers** (object) - Optional - Custom headers for the request.
  - **queryParams** (object) - Optional - Query parameters for the request.

### Request Example
```json
{
  "vendor": "jdbc_rest",
  "vendorName": "jdbc_rest",
  "name": "REST API test",
  "description": "",
  "type": "jdbc",
  "params": {
    "base_URI": "some base URI",
    "connectStrOpts": ""
  },
  "credentialType": "httpQueryBasedAuth",
  "credentials": [
    {
      "queryKey": "user",
      "queryValue": "token"
    }
  ],
  "endpoints": [
    {
      "tableName": "table1",
      "httpMethod": "get",
      "endpoint": "endpoint1",
      "requestBody": "",
      "xPath": "",
      "dataModel": "document",
      "headers": {},
      "queryParams": {}
    }
  ]
}
```

### Response
#### Success Response (200)
- **connectionId** (string) - The unique identifier for the created connection.

#### Response Example
```json
{
  "connectionId": "conn_12345abcde"
}
```
```

--------------------------------

### PAD Function Basic Usage Examples

Source: https://help.alteryx.com/dataprep/en/trifacta-application/wrangle-language/string-functions/pad-function

Demonstrates the basic usage of the PAD function with column references and string literals. It shows how to pad strings to a minimum length with a specified character and side.

```Wrangle
pad(Whse_Nbr, 6, '0', left)
pad('My Name', 10, '!', right)
```

--------------------------------

### cURL Example for Getting Connection ID

Source: https://help.alteryx.com/dataprep/en/developer/api-reference/api-tasks/api-task---manage-outputs

This cURL command demonstrates how to send a GET request to retrieve connection information. It includes the necessary headers for authorization, cache control, and content type.

```shell
curl -X GET \
  http://www.wrangle-dev.example.com/v4/connections \
  -H 'authorization: Basic <auth_token>' \
  -H 'cache-control: no-cache' \
  -H 'content-type: application/json'
```

--------------------------------

### Example Connection String Options for Database Connections

Source: https://help.alteryx.com/dataprep/en/platform/connections/connections-page/create-connection-window

This snippet demonstrates an example of connection string options that can be used when configuring a database connection. These options are specific to certain database types and may be required for special access configurations.

```text
transportMode=http;httpPath=cliservice
```

--------------------------------

### Generate Numeric Array with Column References - Alteryx DataPrep

Source: https://help.alteryx.com/dataprep/en/trifacta-application/wrangle-language/other-functions/range-function

This example shows how to use the 'range' function with column references for start, end, and step values. It dynamically generates an array based on the values present in the 'MaxValue' and 'stepValue' columns, starting from 0.

```Alteryx DataPrep
range(0, MaxValue, stepValue)
```

--------------------------------

### REPEAT Function Example - Padding Values (Wrangle)

Source: https://help.alteryx.com/dataprep/en/trifacta-application/wrangle-language/string-functions/repeat-function

This example demonstrates using the REPEAT function to pad numerical product IDs with leading zeros to ensure a consistent length. It first calculates the number of zeros needed and then uses REPEAT to generate the padding string, which is then merged with the original ID.

```Wrangle
8 - len(prodId)
```

```Wrangle
repeat('0', len_prodId)
```

--------------------------------

### Get Wrangled Datasets (Recipes) - cURL Example

Source: https://help.alteryx.com/dataprep/en/developer/api-reference/api-tasks/api-task---manage-outputs

This cURL command demonstrates how to fetch wrangled datasets using the Alteryx DataPrep API. It includes the necessary GET method, endpoint URL, and headers for authorization and cache control. The response is a JSON object containing details of available wrangled datasets.

```bash
curl -X GET \
  http://www.wrangle-dev.example.com:3005/v4/wrangledDatasets \
  -H 'authorization: Basic <auth_token>' \
  -H 'cache-control: no-cache'
```

--------------------------------

### Get the length of an array

Source: https://help.alteryx.com/dataprep/en/trifacta-application/wrangle-language

Generates a column containing the number of elements in a given array. The example uses a string representation of an array and the ARRAYLEN function to count its elements.

```Alteryx Formula
ARRAYLEN('["red", "orange", "yellow", "green", "blue", "indigo", "violet"]')
```

--------------------------------

### Simple Formula Example - Alteryx Data Prep

Source: https://help.alteryx.com/dataprep/en/trifacta-application/wrangle-language/other-language-topics/language-documentation-syntax-notes

Provides a basic example of a single-row formula transformation in Alteryx. It shows how to define a formula, specify the formula type, and name the new column. This is a foundational example for creating new calculated columns.

```Alteryx Data Prep
Transformation Name| `New formula`
Parameter: Formula type| ```
Single row formula
```
Parameter: Formula| ```
(2 + 3)
```
Parameter: New column name| ```
'five'
```
```

--------------------------------

### Example Microsoft SQL Server Connect String Options

Source: https://help.alteryx.com/dataprep/en/platform/connections/connection-types/mysql-connections/microsoft-sql-server-connections

This example demonstrates how to format multiple connect string options for a Microsoft SQL Server connection. It includes common properties like database name, encryption settings, and login timeout, all structured as a single string with semicolons as delimiters.

```sql
;database=<database_name>;encrypt=true;trustServerCertificate=false;
hostNameInCertificate=*.database.windows.net;loginTimeout=30;
```

--------------------------------

### Alteryx String Check with AND

Source: https://help.alteryx.com/dataprep/en/trifacta-application/wrangle-language/logical-functions/logical-operators

An example of using the AND operator to check if a string meets two criteria simultaneously: starting with 'A' and ending with 'A'. This uses the LEFT and RIGHT functions to examine specific parts of the string.

```Alteryx Formula
((LEFT(USStates,1) == "A") && (RIGHT(USStates,1) == "A"))
```

--------------------------------

### ABS Function - Column Reference Example

Source: https://help.alteryx.com/dataprep/en/trifacta-application/wrangle-language/math-functions/abs-function

Demonstrates how to use the ABS function to get the absolute value of each entry in a specified column. It takes a column name as input and returns the non-negative version of the numbers in that column.

```Alteryx Formula
abs(MyInteger)
```

--------------------------------

### Example: Run Another Job via HTTP Task

Source: https://help.alteryx.com/dataprep/en/platform/plans/plan-tasks/create-a-plan/create-http-task

An example demonstrating how to configure an HTTP task to trigger another job upon successful completion.

```APIDOC
## Example: Run Another Job

### Description
This example shows how to set up an HTTP task to initiate another job after the current one successfully completes. This is useful for creating conditional job execution sequences.

### Prerequisites
- Obtain the recipe identifier for the job you want to run next.

### Configuration Steps
1. **Define the HTTP Task:**
   - **Name:** A descriptive name (e.g., "Trigger Next Job")
   - **Url:** The endpoint URL for triggering jobs (e.g., `http://www.example.com:3005/v4/jobGroups/`)
   - **Headers:** Include necessary headers like `Content-Type: application/json` and `Authorization: Bearer <your_access_token>`.
   - **Body:** Structure the request body to include the target recipe ID. For example:
     ```json
     {
       "wrangledDataset": {
         "id": <recipe_id>
       }
     }
     ```
     Replace `<recipe_id>` with the actual identifier obtained in the prerequisites.
   - **Method:** Set the method to `POST`.

### Verification
- Run the plan containing the HTTP task.
- After successful completion, navigate to the flow of the target job.
- Observe that a new job for the target recipe is queued, in progress, or completed.
```

--------------------------------

### HTTP Request Headers Example

Source: https://help.alteryx.com/dataprep/en/platform/plans/plan-view-page/plan-view-for-http-tasks

Demonstrates how to set HTTP content headers as key-value pairs. This is crucial for specifying the format of the data being sent, such as JSON.

```text
key: Content-Type
value: application/json
```

--------------------------------

### FIND Function: string_to_search Argument Example (Wrangle)

Source: https://help.alteryx.com/dataprep/en/trifacta-application/wrangle-language/string-functions/find-function

Shows examples of valid values for the 'string_to_search' argument in the Wrangle FIND function. This can be a column reference, a string literal, or a function that returns a string.

```Wrangle
'Hello, World'
myColumn
concat(col1, col2)
```

--------------------------------

### Get Second Previous Order Value (Alteryx Formula)

Source: https://help.alteryx.com/dataprep/en/trifacta-application/wrangle-language/language-appendices/transformation-examples/example---prev-function

This Alteryx formula retrieves the order value from two rows prior to the current one, for the same customer. Similar to the previous example, it relies on grouping by 'CustId' and ordering by 'Date'.

```Alteryx
PREV(OrderValue, 2)
```

--------------------------------

### Example Date Format String Input for DATEFORMAT

Source: https://help.alteryx.com/dataprep/en/trifacta-application/wrangle-language/date-functions/dateformat-function

Shows an example of a valid input for the 'date_format_string' argument in the DATEFORMAT function. This string literal defines the desired output format for the datetime values.

```Wrangle
'MM/dd/yyyy'
```

--------------------------------

### Extract Substring from a String

Source: https://help.alteryx.com/dataprep/en/trifacta-application/common-tasks/cleanse-tasks/manage-string-lengths

This snippet shows how to extract a specific portion of a string using the SUBSTRING function. It requires specifying the starting index (0-based) and the number of characters to include. The example extracts 'States' from 'United States of America'.

```Alteryx Formula
SUBSTRING(countries, 7, 6)
```

--------------------------------

### Freshdesk Domain Example

Source: https://help.alteryx.com/dataprep/en/platform/connections/connection-types/early-preview-connection-types/freshdesk-connections

Example of how to set the Freshdesk domain property when creating a connection. The domain is derived from your Freshdesk URL.

```text
https://myco.freshdesk.com
```
Tokens:
Raw