Show TOC

Function documentationData Conversion Maintenance Locate this document in the navigation structure

 

Conversion files define the mapping by dimensions from external member names to internal member names. You create one conversion file per dimension in a transformation.

Each conversion file can contain one or more sheets for different types of data conversions.

Features

*skip

If you want to ignore external data (as listed in the External column), you can place the keyword *skip in the Internal column, as in the following example.

External

Internal

ACCPAY

*skip

Wildcards

You can use the asterisk (*) and question mark (?) wildcards in the External or Internal columns. An asterisk (*) stands for any character, while a question mark (?) stands for any single character. For example, if you want to reference all members, use the asterisk (*).

You also can use a colon (:) for a specified range:

  • When Conversion_internal = NO, use a colon in the Internal column or formula column.

  • When Conversion_internal = YES, use a colon in the External column or formula column.

In the following example, the formula, which uses an asterisk, would increase all of the members by 10 percent:

External

Internal

Formula

*

*

Value*1.10

*IF

You can use if statement in a conversion file.

External

Internal

actual

*If(%external%=act* then Actual; %external% = bud* then budget; forecast)

If the transformation option Convert_internal = NO, *IF can be used in the Internal column.

If transformation option Convert_internal = YES, *IF can be used in the External column.

Formula

To write a formula to update SIGNEDDATA, you write this formula with the FORMULA column.

Example 1

Conversion file: RPTCURRENCY.XLS!SHEET1

External

Internal

USD

*skip

LC

RMB

Conversion file: RPTCURRENCY.XLS!SHEET2

External

Internal

Formula

LC

RMB

Value*1.10

Apply this conversion rule to dimension RPTCURRENCY.

The first rule is applied to RPTCURRENCY, and the second rule is applied to Signedata. The criteria checks the member value of RPTCURRENCY before and after the conversion.

Before the conversion, the transaction data is the following:

DATASRC,CATEGORY,ACCOUNT,ENTITY,TIME,PRODUCT,RPTCURRENCY,AMOUNT

INPUT,ACTUAL,ExtSales,PETSTORE1,2006.JAN,DOGDRYPUPPY,USD,-100

INPUT,ACTUAL,ExtSales,PETSTORE1,2006.JAN,DOGDRYADULT,LC,-200

The meaning of this rule is the following:

  • Convert the dimension members of RPTCURRENCY

    • If the source value is USD, then the system skips this current record since the internal value is *skip.

    • If the source value is LC, then this value is converted to RMB.

  • Convert the SIGNEDDATA according to the member value of RPTCURRENCY

    If the source value is LC before conversion and is RMB after the conversion, then the signedata of this current record is increased by the formula.

After the conversion, the transaction data is the following:

Submitted:

Transaction data:

DATASRC,CATEGORY,ACCOUNT,ENTITY,TIME,PRODUCT,RPTCURRENCY,AMOUNT

INPUT,ACTUAL,ExtSales,PETSTORE1,2006.JAN,DOGDRYADULT,RMB,-220

Skipped:

Transaction data:

DATASRC,CATEGORY,ACCOUNT,ENTITY,TIME,PRODUCT,RPTCURRENCY,AMOUNT

INPUT,ACTUAL,ExtSales,PETSTORE1,2006.JAN,DOGDRYPUPPY,USD,-100

Example 2

Conversion file: RPTCURRENCY.XLS!SHEET1

External

Internal

Formula

*

*

Value*1.10

Apply this conversion rule to dimension RPTCURRENCY.

Before the conversion, the transaction data is the following:

DATASRC,CATEGORY,ACCOUNT,ENTITY,TIME,PRODUCT,RPTCURRENCY,AMOUNT

INPUT,ACTUAL,ExtSales,PETSTORE1,2006.JAN,DOGDRYPUPPY,USD,-100

INPUT,ACTUAL,ExtSales,PETSTORE1,2006.JAN,DOGDRYADULT,LC,-200

The meaning of this rule is: For any member of RPTCURRENCY, whatever it is before the conversion (External) and after conversion (Internal), the formula is applied to the signed data for the current data records.

After the conversion, the transaction data is the following:

Transaction data:

DATASRC,CATEGORY,ACCOUNT,ENTITY,TIME,PRODUCT,RPTCURRENCY,AMOUNT

INPUT,ACTUAL,ExtSales,PETSTORE1,2006.JAN,DOGDRYPUPPY,USD,-110

INPUT,ACTUAL,ExtSales,PETSTORE1,2006.JAN,DOGDRYADULT,LC,-220

Example 3

Conversion file: RPTCURRENCY.XLS!SHEET1

External

Internal

1:100

PETSTORE1

C001:C999

PETSTORE2

Apply this conversion rule to dimension ENTITY.

Before the conversion, the transaction data is the following:

DATASRC,CATEGORY,ACCOUNT,ENTITY,TIME,PRODUCT,RPTCURRENCY,AMOUNT

INPUT,ACTUAL,ExtSales,23,2006.JAN,DOGDRYPUPPY,USD,-100

INPUT,ACTUAL,ExtSales,C100,2006.JAN,DOGDRYADULT,LC,-200

The meaning of this rule is: For the dimension members of ENTITY, if the source value is in the range of the value in the external column, then the source value is converted to the value in the internal column

After the conversion, the transaction data is the following:

Submitted:

Transaction data:

DATASRC,CATEGORY,ACCOUNT,ENTITY,TIME,PRODUCT,RPTCURRENCY,AMOUNT

INPUT,ACTUAL,ExtSales, PETSTORE1,2006.JAN,DOGDRYPUPPY,USD,-100

INPUT,ACTUAL,ExtSales, PETSTORE2,2006.JAN,DOGDRYADULT,LC,-200

Javascript can be used in the data conversion

  • Mandatory: Precede the Javascript code with the following string: js:

  • You can use Javascript code in IF statements.

    Example Example

    The following examples illustrate Javascript string manipulation commands:

    • js: %external%.substring(0,2)

      • This example returns a substring of the string contained in external.

    • js: %external%.length

      • This example returns the length of the string contained in external.

    • js: %external%.replace("apples", "oranges");

      • This example returns a string in which a specified substring has been replaced with another substring a specified number of times.

    • *IF(%external%=act* then Actual; js:%external%.length=4 then budget; forecast)

      • In this example, if the source data contains the patten of act*, then the source data is converted to Actual. If the length of the source data is 4, then the source data is converted to Budget. Otherwise, the source data is converted to Forecast.

    End of the example.

  • Javascript can be used in the FORMULA column

    Example Example

    The following item is an example of a Javascript command that can be used in the formula column:

    • js:Math.round(VALUE*Math.pow(10,2))/Math.pow(10,2);

      If the source value is 200.356, the target value is 200.36.

    End of the example.

  • Javascript is used to generate a target value or to do calculations for signed data. This means the following:

    • When Conversion_internal = NO, Javascript can be used in the Internal column or formula column.

    • When Conversion_internal = YES, Javascript can be used in the External column or formula column.

Activities

You can perform the following tasks when maintaining data conversions:

To maintain conversions, choose Data Maintain conversions.

Any software coding and/or code lines / strings ("Code") included in this documentation are only examples and are not intended to be used in a productive system environment. The Code is only intended to better explain and visualize the syntax and phrasing rules of certain coding. SAP does not warrant the correctness and completeness of the Code given herein, and SAP shall not be liable for errors or damages caused by the usage of the Code, except if such damages were caused by SAP intentionally or by its gross negligence.