issue using excel formula in transformation file

[former_member199935]

Hi,

I'm trying to remove invalid characters from CSV file for this, I'm using following formula but BPC is not accepting.

Is there something wrong?

Are there any better ideas (apart from using formula in transformation file) to remove invalid characters?

MATERIAL=*SUBSTITUTE ( *COL(3), "-", "")

MATERIAL dimension has a member of "testmat". if CSV file is trying to load a value of "test-mat", I would like to replace CSV file with "testmat".

So, Basically, I would like to remove all characters of "-" for dimension MATERIAL in my csv file with a blank character.

Also, are there any limitations on which excel formulas we can use in transformation file? Is there any online documention on this.

Appreciate any help.

[JohnL]

Hi there,

You can't do this in the Transformation file. However, you can use the visual basic in the conversion file for this.

Here's some info from the help file:

Visual Basic in formulas

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

You can use Visual Basic code inside IF statements, too. The IF statement has the following definition:

If(A1=B1 then c1; A2 = B2 then C2; ... ; ELSE D)

This statement executes the first condition that evaluates to TRUE. If none of the conditions are true, and you have defined an ELSE section, that condition is used.

Example: If(%external%=act* then Actual; %External% = bud* then budget; ELSE forecast)

Conversion Examples:

EXTERNAL,INTERNAL

a,If(%external%=act then Actual; vb:len("%external%")=4 then budget;Actual)

b*,If(%external%=BCT then Act???; vb:len("%external%")=1 then budget;Budget)

c*,vb: replace("%external%","act","bud")

Formula Examples:

Formula,Description

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.

[former_member199935]

John,

VB:replace may work in my scenario but I've another question.

Do I have to include a formula for each dimension member in my conversion file?

because, we don't know which dimension member has invalid characters. Is there a procedure that I can use to apply single VB replace formula for all of dimension members?

[JohnL]

You can use the asterisk (*) and question mark (?) wildcards in the External and/or Internal columns.

The asterisk (*) stands for ANY character, while question mark (?) stands for any SINGLE character.

Goodluck,

John

[former_member199935]

John,

I tried following way and got error. I know that this may not work but I can't think of any other options other than this.

in the conversion file, I've following.

EXTERNAL	INTERNAL	FORMULA
*-*	              **	

Error I'm getting for the above conversion file is....

Argument 'Length' must be greater or equal to zero.

Do you have any other recommendations? Basically,

I've following dimension members in my MATERIAL dimension

TESTMAT
TESTMTL
TESTMM
TESTMATN

If my CSV file has following values, how can i fix the issue either by using "transformation" or "conversion" files? are there any other ways of fixing this without using formulas in "transformation" or "conversion" files?

CSV file has following values.

TEST-MAT
TESTM-TL
TESTM-M
TE-STMATN

[asad_ahmed2]

Hello

If you do not have too many members in your Material dimension you can simply write all the external values in column 1 and internal values which are equal to them for BPC in column 2.

You can also use Jon's VB formula and replace "-" with nothing.

Thanks.