Conversion file question

[Former Member]

Hi folks,

I am having a problem, and not sure how to go about fixing it - I've tried a couple of options, but neither work.

We have transaction data supplied to us in an Info Provider in BW, and I want to load this into BPC with the standard packages, transformations and conversion files.

The source data has a cost centre field (called 0PERS_SAREA), but it's not consistent, so a value could be 4898 (which is correct), but sometimes it is padded with a leading zero (0605 for example, which is wrong; it should be simply 605) or even padded with a leading "E" (E123 for example, which is also incorrect; 123 would be the expected value).

I don't have access to amend this field, or create a new BW field, so I want to clean this value with a conversion file. I am trying to use Javascript in the internal column, like this:

EXTERNAL	INTERNAL	FORMULA
0*	JS:%EXTERNAL%.substr(1,3)
E*	JS:%EXTERNAL%.substr(1,3)
*	*

So in English, I'm trying to say IF the field value starts with a zero, ignore the first character and just take the other 3 (the field is only 4 characters long).

IF the field value starts with an "E", ignore the first character and just take the other 3.

Otherwise, any other value is valid and OK.

But when I run this, I get an error "JS: %EXTERNAL%.substr(1,3) evaluation error" - no other info is given.

I've used the transformation option CONVERT_INTERNAL and tried both YES and NO values - no effect.

*OPTIONS

FORMAT = DELIMITED

HEADER = YES

DELIMITER = ,

AMOUNTDECIMALPOINT = .

SKIP = 0

SKIPIF =

VALIDATERECORDS=YES

CREDITPOSITIVE=YES

MAXREJECTCOUNT=

ROUNDAMOUNT=

CONVERT_INTERNAL=NO

*MAPPING

ENTITY=0PERS_SAREA

*CONVERSION

ENTITY=ENTITY.xls

I also tried to use an IF statement in a single row, where EXTERNAL is * like this:

*IF(%external%==0??? then js:%external%.substr(%external%,1,3); %external%==E??? then js:%external%.substr(%external%,1,3); %external%)

(I tried a few variations of this, still no luck. All that happens is the string "%external%" gets added into the transformed file).

Am I barking up the wrong tree here? Or have things changed that dramatically since 7.5NW?

Hope that makes sense - any ideas welcome! I've attached (tab-delimited versions of) the conversion and transformation files to this post if you need them.

Thanks,

Jason

0LSO_C01.txt.zip

ENTITY.txt.zip

[Former Member · Accepted solution]

Hi Jason,

All you need is .toString() in between JS:%EXTERNAL% and .substr()

everything else looks correct.

Andy

[Former Member · Accepted solution]

Thanks Andy - that should do the trick nicely.

Hope you're doing well?

Thanks,

Jason

[former_member186338 · Accepted solution]

Hi Jason,

If you look on my answer

you will see that 4. js:%external%.toString().substr(1,3)

Andy simply repeat it.

Vadim

[former_member186338]

Single line:

js:/[E0]/i.test(%external%.toString().charAt(0)) ? %external%.toString().substr(1,3) : %external%.toString()

/[E0]/i.test(%external%.toString().charAt(0)) - will test the first character of %external% to be "E" or "0"

Without test for single "0" or "E"

Vadim

[Former Member]

Thanks Vadim, it's been more than 10 years since I've worked with JS so I guess I need a refresher course!

Appreciate the help and examples, very interesting.

Jason

[former_member186338]

1. BPC and BW full SP info!

2. In the attached transformation file what do you mean by:

*OPTIONS   

FORMAT = DELIMITED   

HEADER = YES   

"DELIMITER = ,"   

3. Please read

and some online Javascript tutorial.

4. Try:

js:%external%.toString().substr(1,3)

5. Do you have members like "0" or "E"? Error if yes.

Vadim