Example: Bring in International-Formatted Numeric Fields

This example describes how to use Prism Analytics to bring in external data that includes a numeric field containing International-formatted numbers.

Your company has a delimited file with numeric data formatted using periods to separate thousands and commas to separate decimals. You want to bring this data into Prism Analytics as a Numeric field so that you can perform calculations on the data. However, Workday only recognizes data in an external file as valid numeric data when it:

Uses a period as the decimal mark.

Doesn't use any character to separate thousands.

You have a comma delimited (CSV) file with this data:

Key,Amount
1,"9.000,1222-"
2,"11.111,2333"
3,"9.999.999,34-"
4,"7.777.777,45"
5,"8.888,56"

In your file, no value has more than 4 digits after the decimal mark.

Create a table by file upload, using the CSV file to define the source schema. The table should have these fields:

Key

of type Numeric

Amount

of type Text

Create a derived dataset based on the table, and use DDS Intl as the dataset name.

Security:

Prism: Tables Create
domain in the Prism Analytics functional area.

Prism: Manage File Containers
domain in the Prism Analytics functional area.

Prism Datasets: Create
domain in the Prism Analytics functional area.

Prism Datasets: Manage
domain in the Prism Analytics functional area.

Access the

Edit Dataset Transformations

task for the DDS Intl dataset.

Create a Prism calculated field that removes the thousands separators by replacing any period with an empty string.

Select

Add Field

Enter this expression in the expression editor, and press Enter or Return on your keyboard:

REGEX_REPLACE([Amount],"\.","")

In the inspector panel, enter this as the field name:

Amount no thousands

Create a Prism calculated field that replaces the decimal comma with a decimal period.

Select

Add Field

Enter this expression in the expression editor, and press Enter or Return on your keyboard:

REGEX_REPLACE([Amount no thousands],",",".")

In the inspector panel, enter this as the field name:

Amount decimal period

Create a Prism calculated field that adds any negative sign that exists at the end of the value to the front of the value.

Select

Add Field

Enter this expression in the expression editor, and press Enter or Return on your keyboard:

CASE WHEN SUBSTRING([Amount decimal period], LENGTH([Amount decimal period])-1, LENGTH([Amount decimal period])) = "-"
THEN CONCAT("-",REGEX_REPLACE([Amount decimal period],",","")) 
ELSE [Amount decimal period]
END

In the inspector panel, enter this as the field name:

Amount minus sign

Create a Prism calculated field that removes any minus sign at the end of the value.

Select

Add Field

Enter this expression in the expression editor, and press Enter or Return on your keyboard:

CASE WHEN SUBSTRING([Amount minus sign], LENGTH([Amount minus sign])-1, LENGTH([Amount minus sign])) = "-"
THEN SUBSTRING([Amount minus sign], 0, LENGTH([Amount minus sign])-1)
ELSE [Amount minus sign]
END

In the inspector panel, enter this as the field name:

Amount final

Create a Prism calculated field that converts the [Amount final] Text field to a Numeric field.

Select

Add Field

Enter this expression in the expression editor, and press Enter or Return on your keyboard:

CAST([Amount final] AS Decimal(20,4))

In the inspector panel, enter this as the field name:

Amount Numeric

Select

Add Stage

and then select

Manage Fields

Hide these fields by clicking the eye icon:

Amount

Amount no thousands

Amount decimal period

Amount minus sign

Amount final

Select

Done

Save

your dataset.

The dataset contains these fields:

Key as type Numeric

Amount Numeric as type Numeric, with 20 digits before the decimal and 4 digits after.