Transformation/Conversion

[borja_garayzar_zorrilla]

Hello experts.

I am working with SAP BPC MS 7.5 SP11.

I am facing a new situation in which I need to do some data conversions depending on two dimensions. I need to change the ACCOUNT but using different rules depending on the LEGALENTITY.

Initial data:

LEGALENTITY,CATEGORY,ACCOUNTL,DATASRC,VALUE

10001000,ACTUAL,100000,REPO,123456

13001310,ACTUAL,100010,REPO,456789

13001310,ACTUAL,100020,REPO,890123

13001320,ACTUAL,100010,REPO,100

13001320,ACTUAL,100020,REPO,987654

I would like to know if it is posible to handle in a single conversion file three different mapping rules. One for company 13001310, one form company 13001320 and one for all the other companies.

Result:

LEGALENTITY,CATEGORY,ACCOUNTL,DATASRC,VALUE

1000,ACTUAL,100000,REPO,123456           (shortened the legalentity and no changes in account)

1300,ACTUAL,200020,REPO,456789           (shortened  the legalentity and the accountl using mapping1)

1300,ACTUAL,200040,REPO,890123           (shortened the legalentity and the accountl using mapping1)

1300,ACTUAL,300035,REPO,100                 (shortened the legalentity and the accountl using mapping2)

1300,ACTUAL,300036,REPO,987654           (shortened the legalentity and the accountl using mapping2)

Is this posible?

Thanks very much in advance for your help!

Borja G.

[Former Member · Accepted solution]

Hi Borja,

I think it should be possible, in your transformation file you would use

ENTITY=*COL(1)

ACCOUNT=*COL(1)+*COL(3)

and then you could write an account mapping based on the combination of entity and account.

BR,
Arnold

[borja_garayzar_zorrilla · Accepted solution]

Thanks Arnold for your approach.

The problem is that this option would be very 'static' since I would have to write a line for every combination of ENTITY+ACCOUNTL in the conversion file.

Alternatively, I could do two different imports to isolate the problem . Is there a way of using different conversion files deppending on just one dimension?

[Former Member · Accepted solution]

Hi,

you can work with * and ? to make it a bit easier, so you would specifically mention the exceptions at the top and then use placeholders at the bottom of the conversion file to handle the main rule.

BR,
Arnold

[borja_garayzar_zorrilla · Accepted solution]

Arnold,

Thanks for your reply. I almost got it. I have done as you were suggesting:

1) Transformation file:

*OPTIONS

FORMAT = DELIMITED

HEADER = YES

DELIMITER = ;

SKIP = 0

SKIPIF =

VALIDATERECORDS=YES

CREDITPOSITIVE=YES

MAXREJECTCOUNT=-1

ROUNDAMOUNT=NO

AMOUNTDECIMALPOINT= ,

*MAPPING

ACCOUNTL=*COL(2)+*STR(-)+*COL(1)

*CONVERSION

IntCol=Intcol.xls

Accountl=Accountl_Test6.xls

Legalentity=Legalentity.xls

Acctdetail=Acctdetail.xls

2) Conversion file:

EXTERNAL	INTERNAL	FORMULA
13001310-212060	212070
*-212060	212060
*	*

It is working for all the lines except for the lines in red (register with accounts different of 212060). I get an error like

Task Name : Convert Data

[Dimension:  ACCOUNTL]

2003-100000             (this is logical, since there is no account like that, but only 100000)

Any idea on how could I get only the account part? (instead of the combination of LEGALENTITY+ACCOUNTL?)

[Former Member · Accepted solution]

Hi,

technically I can see three options:

use vb script to only use the last 6 characters in the INTERNAL column

switch the columns and then use ?????? in the INTERNAL column

both only work if all your accounts are 6 characters.

actually fill in the conversion file for all accounts, so to keep with your example you would need to add a row

EXTERNAL     INTERNAL

*-100000          100000

[former_member186498 · Accepted solution]

Hi Borja,

if the second part is always 6 chars you can also try

EXTERNAL     INTERNAL

*-??????          ??????

Regards

     Roberto

[borja_garayzar_zorrilla · Accepted solution]

Thanks Arnold & Roberto for your reply.

My accounts are not always 6 digit, so I am still trying with VB inside the conversion file.

I am trying something like:

vb:RIGHT("%EXTERNAL%",vb:LEN("%EXTERNAL%")-vb:SEARCH("-","%EXTERNAL%"))

but getting some kind of syntax error.

Could you please provide any link about how to use VB inside conversions? (I just can´t find it anywhere...).

Thanks.

[former_member186498 · Accepted solution]

Hi Borja,

I think you should use the InStr function, try

vb:RIGHT(%external%,LEN(%external%)-INSTR("-",%external%)+1)

Regards

     Roberto

[borja_garayzar_zorrilla · Accepted solution]

Thanks so much for your help, Roberto,

I am trying what you suggested but for some reason it doesn´t work.

I am trying to shorten your instruction to try to understand where does the problem come from and, for instance:

EXTERNAL	INTERNAL
13001310-212060	212070
*-212060	212060
*	vb:right(%external%,6)

My .csv is this,

ACCOUNTL	LEGALENTITY	ACCTDETAIL	CATEGORY	DATASRC	GROUPS	INTCOL	TIME	MEASURES
212060	13001310	900	STRESS	REPO	LC	NON_INTERCO	2014.AUG	1
212060	10001000	900	STRESS	REPO	LC	NON_INTERCO	2014.AUG	1
212060	2003	900	STRESS	REPO	LC	NON_INTERCO	2014.AUG	1
100000	2003	900	STRESS	REPO	LC	NON_INTERCO	2014.AUG	1

The first 3 lines went o.k. but the last one didn´t. It is coming with an error of:

Task Name : Convert Data

[Dimension:  ACCOUNTL]

-97997   (instead of converting to 100000 as expected, it is making the calculation 2003-100000=-97997  !!!

Any ideas why it is making this?

[Former Member · Accepted solution]

Hi,

maybe use a different sign instead of - as this might be seen as a mathematical operator. I tend to use letters such as q which I normally do not use in my IDs.

[former_member186498 · Accepted solution]

Hi Borja,

yes as Arnold wrote it's better to use in the input file "_" for separator or other signs different from the operators used by excel.

Regards

     Roberto

[borja_garayzar_zorrilla · Accepted solution]

Roberto, Arnold.

I got it by changing the separator and using instr.

It is surprinsing that I can´t see in the BPC help any reference to VB in transformation/conversion files.

Thanks very much for your help.

Borja G.

[former_member186498 · Accepted solution]

Hi Borja,

you're right, I think because they are the typical function of VB and SAP don't won't to duplicate the information, for maintainance and copyright, but a link on MS pages could help.

For the future see please VB functions

Regards

     Roberto