> For the complete documentation index, see [llms.txt](https://docs.collibra.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.collibra.com/data-quality-self-hosted/core-concepts/findings/data-quality-monitors/job-tab/job-command-line-parameters/advanced/transform.md).

# Transform

### Transform Date

When you create a dataset, you can transform columns such as Dates and Numbers to preferred formats. It is a common need to replace N.A. with nulls or empty white space.

{% hint style="info" %}
Because the Transform option defaults to a most common use case of `to_date`, you need to determine the proper transform function required for the data source in use.
{% endhint %}

![Transform settings](https://4121921244-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FWCrP7Albs8DfJjIQxUxw%2Fuploads%2Fgit-blob-102b9f5146bd020b1ad49a54818a73fcf9d207c2%2Fdq-transform-settings.png?alt=media)

<table><thead><tr><th width="152">Option</th><th>Description</th></tr></thead><tbody><tr><td>Add Collibra DQ Date Column</td><td><p>Adds a synthetic run date column.</p><div data-gb-custom-block data-tag="hint" data-style="success" class="hint hint-success"><p>This can be helpful when your file does not contain dates that are easy to handle.</p></div></td></tr><tr><td>Flatten</td><td><p>Refers to the level of nesting in your file.</p><div data-gb-custom-block data-tag="hint" data-style="success" class="hint hint-success"><p>Data Quality &#x26; Observability (self-hosted) traverses all levels, which can result in highly nested data being expanded into many rows per nested object. This can significantly impact processing performance, so use this feature with caution.</p></div></td></tr><tr><td>Multi-line</td><td><p>Refers to the content of the file being more than one line.</p><div data-gb-custom-block data-tag="hint" data-style="info" class="hint hint-info"><p>There are limitations in Explorer for <a href="/data-quality-self-hosted/connections/supported-connections/supported-remote-files.md">remote file systems</a>, file uploads, and temporary files. The user interface doesn't support multi-line input for these file types, but you can manually supply them using using the command line.</p></div></td></tr><tr><td>Zero Fill Null</td><td>Replaces NULL values with 0.</td></tr><tr><td>Replace all field values of [value] with NULL</td><td>Replaces all values of the column you specify with NULL.</td></tr></tbody></table>

## Example

```
./owlcheck \
-ds  "dataset_transform" \
-rd  "2018-01-31" \
-f   "/Users/Documents/file.csv" \
-transform "purch_amt=cast(purch_amt as double)|return_amt=cast(return_amt as double)" 
```

{% hint style="info" %}
Submit an expression to transform a string to a particular type.In this example, transform the purch\_amt column to a double.
{% endhint %}

```
-transform "purch_amt=cast(purch_amt as double)"
```

{% hint style="info" %}
Example of converting a string to a date.
{% endhint %}

```
-transform "RECEIVED_DATE=to_date(CAST(RECEIVED_DATE AS STRING), 'yyyyMMdd') as RECEIVED_DATE"
```

### Converting an integer data type to a string

When you transform your target column is an integer data type, such as 20230414, you need to convert it to a string data type before you can convert it to a date.

{% stepper %}
{% step %}
From the Explorer page, select the **Transform** option.
{% endstep %}

{% step %}
In the **Expression** input field, enter the following expression:

```
date_parse(cast(<your_column_name> as varchar), '%Y%m%d')
```

{% endstep %}

{% step %}
Click **Save**.

{% hint style="info" %}
If your query does not contain a ${rd} in the where clause, you need to wrap it with the DATE() function in the command line. For example, `select * from default.dataset where date_parse(CAST(example_date_column as varchar), '%Y%m%d') = DATE('${rd}')`
{% endhint %}
{% endstep %}
{% endstepper %}

## Known Limitations

* The UI does not run multi-line. A workaround is to supply these options manually in the command line.
* Explorer can flatten nested JSON array, but DQ Core can only flatten nested JSON with JSON object. Because of this limitation, you may encounter a scenario where the results in Explorer are flattened, but not in DQ Core.