Formula in conversion file..

[Former Member]

In the conversion file for dimemsion members...there are 3 header columns.

EXTERNAL

INTERNAL

FORMULA

What is the formula used for ? has some one used it before...

For example I am having a dimension member coming from external system in 6 digits and I need to strip

the first 2 decimal which are leading zero's..

Where this is best addressed ..conversion file or transformation file?

For example 00ABCDEF, need to be converted as ABCDEF..

Appreciate inputs..

[Former Member · Accepted solution]

Hi,

The best practice to do this is in the Transformation file.

You can specify it in the *MAPPING Section in the Transformation file.

You can use the below format

*COL(A,B:C)
A = column index in the data file
B:C = start/end position within the column

According to your example, the first 2 characters need to be removed out of total 6 characters. It will look something like

*COL(2,3:6)
where the first parameter (2) stands for the index of the column for which you want to do the trimming

Hope this helps.

[Former Member]

Hi Doodwala,

Visual Basic in formulas

You can use simple Visual Basic syntax in formulas. Precede the Visual Basic code with:

vb:

Formula

vb: len("%external%")

Returns a Long containing the number of characters in a string

vb: mid("%external%",1,2)

Returns a Variant (String) containing a specified number of characters from a string

vb: replace("%external%","a","c")

Returns a string in which a specified substring has been replaced with another substring a specified number of times.

For more info you might want to check the Using formulas in conversion files in the Conversion Maintenance Help in BPC for Excel.

Cheers(^^,)y,

MVS