cancel
Showing results for 
Search instead for 
Did you mean: 

MVAL and MKEY functions in transformation files

akos_beres
Contributor
0 Kudos

Everyone,

I'm trying to simplify loading an extract from a GL Rapidmart. The extract has two data columns 'ACCOUNT_BALANCE_LOC' as local currency and ACCOUNT_BALANCE_TRX as converted USD balances. Currently, I load the same file twice first for LC and then for USD. I tried using the MVAL and the MKEY functions but receiving errors. I'd appreciate any suggestions! (System info - BPC MS 10 EPM Add-in SP08 Patch 4 NET 3.5 build 7328)

Sample of the data file:

Transformation file:

*OPTIONS
FORMAT = DELIMITED
HEADER = YES
DELIMITER = COMMA
SKIP = 0
SKIPIF =
VALIDATERECORDS=YES
CREDITPOSITIVE=NO
MAXREJECTCOUNT=
ROUNDAMOUNT=6
*MAPPING
ACCOUNT=ACCOUNT_NUMBER
AUDITTRAIL=*NEWCOL(SAP_GLRM)
CATEGORY=*NEWCOL(ACTUAL)
COST_CENTER=*if(COST_CENTER=*str() then *str(CC_PROFITCEN); *str(CC_)+COST_CENTER(2:9))
PROFIT_CENTER=PROFIT_CENTER
RPTCURRENCY=MKEY(ACCOUNT_BALANCE_LOC|*NEWCOL(LC)||ACOUNT_BALANCE_TRX|*NEWCOL(USD))
TIME=FISCAL_YEAR+*str(.)+PERIOD
VERSION=*NEWCOL(VER_FI)
AMOUNT
*CONVERSION
ACCOUNT=Account.xls!CONVERSION
PROFIT_CENTER=Profit_Center.xls!CONVERSION
TIME=Time.xls!CONVERSION

Transformation file error:

[line 18] Unexpected 'ACCOUNT_BALANCE_LOC' at position 6 in the mapping formula [MKEY(ACCOUNT_BALANCE_LOC|*NEWCOL(LC)||ACOUNT_BALANCE_TRX|*NEWCOL(USD))]: 'INT' is expected

Validation of mapping failed; check the worksheet for highlighted errors

This transformation file works for individual loads:

*OPTIONS
FORMAT = DELIMITED
HEADER = YES
DELIMITER = COMMA
SKIP = 0
SKIPIF =
VALIDATERECORDS=YES
CREDITPOSITIVE=NO
MAXREJECTCOUNT=
ROUNDAMOUNT=6
*MAPPING
ACCOUNT=ACCOUNT_NUMBER
AUDITTRAIL=*NEWCOL(SAP_GLRM)
CATEGORY=*NEWCOL(ACTUAL)
COST_CENTER=*if(COST_CENTER=*str() then *str(CC_PROFITCEN); *str(CC_)+COST_CENTER(2:9))
PROFIT_CENTER=PROFIT_CENTER
RPTCURRENCY=*NEWCOL(LC)
TIME=FISCAL_YEAR+*str(.)+PERIOD
VERSION=*NEWCOL(VER_FI)
AMOUNT=ACCOUNT_BALANCE_LOC
*CONVERSION
ACCOUNT=Account.xls!CONVERSION
PROFIT_CENTER=Profit_Center.xls!CONVERSION
TIME=Time.xls!CONVERSION

Thanks in advance!

Akos

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Akos,

MKEY is available only in the NW platform, not in the MS version.

For loading multiple keyfigures, you need to run the DM package multiple times, once for each of the key figure.

Hope this helps.

Answers (3)

Answers (3)

Former Member
0 Kudos

Hello Akos,

I know that this question is answered, but I would like to talk to you about the TIME line.

Were you able to user this option without an error?

TIME=FISCAL_YEAR+*str(.)+PERIOD

I'me having trouble with that line.

Thank you

Raquel

akos_beres
Contributor
0 Kudos

Raquel,

That works as long as your column headers are FISCAL_YEAR and PERIOD. I would check if there are additional spaces before or after in the column headers.

Akos

Former Member
0 Kudos

Hello Akos,

Could you tell me where do you find the documentation about those "standard" column header?

Thank you so much.

Best regards,

Raquel

akos_beres
Contributor
0 Kudos

Raquel,

the column headers will depend on your source system. This is a Microsoft forum so there will be more variation here than on the BW\NW stack. For example, in FI the standard field name for time period is RFISCPER and the InfoObject is called 0FISCPER in BW if standard extractors are used. On the other hand, on MS side depending on the ETL tool and where you are getting FI data the column header for fiscal period could be different. The example that I posted originally used Dataservices and FI Universe to feed MS BPC. Hope this makes sense!

Akos

former_member186498
Active Contributor
0 Kudos

Hi Akos,

I quite sure that even in BPC 10 *MKEY doesn't exists, surely until 7.5 *MKEY is not available for MS only for NW, in MS version *MVAL works only for the time dimension, so you must flatten your file and split your records to load the two different currencies.


Kind regards

     Roberto

former_member210696
Active Contributor
0 Kudos

Hello Akos,

I think it's just a typo ('*' missing before MKEY command) -

RPTCURRENCY=MKEY(ACCOUNT_BALANCE_LOC|*NEWCOL(LC)||ACOUNT_BALANCE_TRX|*NEWCOL(USD))

should actually read -

RPTCURRENCY=*MKEY(ACCOUNT_BALANCE_LOC|*NEWCOL(LC)||ACOUNT_BALANCE_TRX|*NEWCOL(USD))

Rest everything looks OK.

Hope it helps!!