Excel Formula in Conversion File

[Former Member]

dear BPC Gurus

I've created a DMP to import transaction data from BW.

In the BW Cube, one of the field is called 0FISCPER (Fiscal Year / Period), which the period values are as follows:

2016001

2016002

2016003...

Like this format: YYYYMMM

Now, I want to map this field to my BPC Model TIME Dimension which has the following members:

2016.01

2016.02

2016.03....

In this format: YYYY.MM

In the convesion file, I want to change to the format from YYYYMMM to YYYY.MM by using this code:

TIME = LEFT(0FISCPER,4) + *STR(.) + RIGHT(0FISCPER,2)

However when validating it, it gave me an error message that the formula is not recognized.

I do not want to use a Conversion file for this purpose. Does anyone knows what formula to use for this? Javascript will do too.

[former_member186338 · Accepted solution]

Hi Edmund,

You can't use Excel formulas in the conversion file. Use JavaScript

[former_member186338 · Accepted solution]

Without JavaScript you can perform conversion directly in the transformation file:

TIME=0FISCPER(1:4) + *STR(.) + 0FISCPER(6:7)

[former_member225135]

Hi Edmund,

You can try something like this:

EXTERNAL	INTERNAL
????001	js:(%external%.toString().substring(0,4))+'.01'

Regards,

Rahul

[former_member186338]

Absolutely strange proposal... to specify 12 lines instead of 1...

[Former Member]

Hi Edmund,

Excel formulas cant be used in Conversion file......But for your requirement you can use

TIME = 0FISCPER(1:4)+*STR(.)+0FISCPER(6:7) mapping in the transformation file itself....Assuming TIME is your time dimension name..

Hope this helps....

Regards,

JP

[Former Member]

P.S If you are keen on using Conversion file then use this

External      Internal

*                       js:%external%.substring(0,4) + '.' + js:%external%.substring(6,2)       

Regards,

JP

[former_member186338]

Incorrect JS!

Correct:

js:%external%.toString().substring(0,4)+"."+%external%.toString().substring(5)

[Former Member]

Thank you for correcting.......

Regards,

JP

[former_member186338]

Is it so hard to check the formula in the online JavaScript editor like: http://plnkr.co/edit/?p=preview

[Former Member]

No Vadim.....I had read the help about javascript but not tested since my access is restricted to certain sites only! But no excuses for the mistake!

Regards,

JP

[former_member186338]

Ups, "since my access is restricted to certain sites only"