Create calculated column for the below requirement concatenate 01 to the FSCL_YR AND FSCL_MNTH in SAP Datasphere
In SAP Datasphere (formerly known as SAP Data Warehouse Cloud), you can create a calculated column to achieve your requirement of concatenating "01" to the `FSCL_YR` and `FSCL_MNTH` fields. Assuming `FSCL_YR` represents the fiscal year and `FSCL_MNTH` represents the fiscal month, you would typically want to format the resulting value as a date in the format `YYYY-MM-DD`.
Here’s how to create a calculated column in SAP Datasphere:
1. **Open your Model**:
Start by opening the model where you want to create the calculated column.
2. **Add a Calculated Column**:
Look for the option to add a new calculated column. This is usually done under the "Columns" section of the model.
3. **Write the Calculation**:
Use the following expression to concatenate "01" to the `FSCL_YR` and `FSCL_MNTH`:
```sql
CONCAT(TO_VARCHAR(FSCL_YR), '-',
LPAD(TO_VARCHAR(FSCL_MNTH), 2, '0'),
'-01')
```
Here’s a breakdown of the components:
- `TO_VARCHAR(FSCL_YR)`: Converts the fiscal year to a string.
- `LPAD(TO_VARCHAR(FSCL_MNTH), 2, '0')`: Converts the fiscal month to a string and pads it with zeros on the left if necessary, ensuring it is always two digits (i.e., '01' for January).
- The `CONCAT` function combines all parts with hyphens (`-`) to format it like a date.
4. **Name the Column**:
Provide a suitable name for the new calculated column, such as `CONCATENATED_DATE`.
5. **Save Changes**:
Make sure to save your changes to apply the new calculated column.
### Example:
If `FSCL_YR` is `2023` and `FSCL_MNTH` is `2`, the calculated column will produce `2023-02-01`.
### Important Note:
Make sure that your environment supports the functions used in the expression, as there may be variations in syntax based on the specific features of SAP Datasphere or any versioning aspects. Always verify the resulting data to ensure correctness.