on 05-21-2013 12:16 AM
Hello,
We are planning to use flat file to upload budget values into BPC cube using standard Import package and user wants to budget for more than one years.
User propsed a file format which is handy for them while entering budget values. In his format all 12 months are in columns and year as row. I know we can use MVAL funtion to handle multiple key figures to upload but is there any function or workaround in transformation file to handle to upload this kind of file without using BADI or excel macros.
PROJECT | Version | YEAR | JAN | FEB | MAR | APR | MAY | JUN | JUL | AUG | SEP | OCT | NOV | DEC | Total |
PRJ01 | Budget | 2013 | 1 | 2 | 3 | 5 | 6 | 7 | 8 | 88 | 99 | 89 | 45 | 56 | 409 |
PRJ01 | Budget | 2014 | 2 | 3 | 4 | 6 | 7 | 8 | 9 | 89 | 100 | 90 | 46 | 57 | 421 |
Thanks,
Srinivas Thota
Hi Guys,
What about the BLANK months? Did anyone of you experience differences loading 0 or blanks? In my case, if i have a blank for any month, system is loading a 0 instead of rejecting that missing number.
In BPC7, we used that rejections to avoid modifications on certain months. But now, in EPM10, if we load blanks, they turn into 0.
Thanks in advance
Luisma
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi all,
I have found a workaround to this issue that works. You can try something like:
TIME=*MVAL (JAN | *IF(*STR(1) = *STR(1) THEN YEAR+*STR(.01)) || FEB | *IF(*STR(1) = *STR(1) THEN YEAR+*STR(.02)) )
Using a dummy *IF allows to concatenate year ant the string for the month. Hope we can have a offcial solution from SAP to this issue.
Thanks,
CarlosE
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Please try
TIME=*MVAL(*COL(4)|*STR(*COL(3))+*STR(.JAN)......
Regards,
Kalyan.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Srinivas,
I spent a lot of time on this today, but could not find solution, as per document year+*STR(.01) should work, but it does not. SAP has to look into it and provide solution.
But, in the meantime u may implement workaround like BADI or VBA to process the file and make it ready for load.
Regards,
Kalyan.
Srinivas,
There are several ways to do this but I think the most straight forward is as follows...
Below is an example of the complete transformation file.
*OPTIONS |
FORMAT = DELIMITED |
HEADER = NO |
DELIMITER = , |
AMOUNTDECIMALPOINT = . |
SKIP = 1 |
SKIPIF = |
VALIDATERECORDS=YES |
CREDITPOSITIVE=YES |
MAXREJECTCOUNT=0 |
ROUNDAMOUNT= |
*MAPPING |
PROJECT=*COL(1) |
VERSION=*COL(2) |
TIME=*MVAL(*COL(4)|*COL(3)+*STR(.JAN)||*COL(5)|*COL(3)+*STR(.FEB)||*COL(6)|*COL(3)+*STR(.MAR)||*COL(7)|*COL(3)+*STR(.APR)||*COL(8)|*COL(3)+*STR(.MAY)||*COL(9)|*COL(3)+*STR(.JUN)||*COL(10)|*COL(3)+*STR(.JUL)||*COL(11)|*COL(3)+*STR(.AUG)||*COL(12)|*COL(3)+*STR(.SEP)||*COL(13)|*COL(3)+*STR(.OCT)||*COL(14)|*COL(3)+*STR(.NOV)||*COL(15)|*COL(3)+*STR(.DEC)) |
ACCOUNT=*NEWCOL(A_56790873) |
DATASRC=*NEWCOL(UPLOAD) |
ENTITY=*NEWCOL(CC_22221) |
*CONVERSION |
This is a working example based on SAP BPC 7.5 NW SP11
Documentation used to created *MVAL function
http://help.sap.com/saphelp_bpc75_nw/helpdata/en/b8/a76a1ca9ac4ca698259a8ff397bb61/frameset.htm
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Jonathan,
Thanks for the detailed information,
I tried this option but i am not able to validate transformation file. I am getting below error when I am validating without transaction data.
Its not able to validate the "+" in the syntax.
[line 16] Unexpected '+' at position 23 in the mapping formula [*MVAL(*COL(4)|*COL(3) + *STR(.JAN)||*COL(4)|*COL(3) +*STR(.FEB))]: '')'' is expected
Any inputs?
Hi Srinivas,
BPC have a function called MVAL where you can use to catch values reading the time in the header. So instead of you write, ig. 12 lines (considering a year) you can write only a one line, putting the period in the header.
In your case, you can try using *MVAL for 24 periods.
Your file will have the following format:
PROJECT | Version | 2013.JAN | 2013.FEB | 2013.MAR | 2013.APR | 2013.MAY | 2013.JUN | 2013.JUL | 2013.AUG | 2013.SEP | 2013.OCT | 2013.NOV | 2013.DEC | 2014.JAN | 2014.FEB | 2014.MAR | 2014.APR | 2014.MAY | 2014.JUN | 2014.JUL | 2014.AUG | 2014.SEP | 2014.OCT | 2014.NOV | 2014.DEC |
PRJ01 | Budget | 1 | 2 | 3 | 5 | 6 | 7 | 8 | 88 | 99 | 89 | 45 | 56 | 2 | 3 | 4 | 6 | 7 | 8 | 9 | 89 |
So in your transformation file you mapp:
Time = *MVAL(3:26)
Please refer:
http://help.sap.com/saphelp_bpc70/helpdata/en/5d/9a3fba600e4de29e2d165644d67bd1/content.htm
Best Regards,
Lucas
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Shinivas.
The user can wish upload the file in that format, but is recomended that the user upload the file in the traditional way, because the transformation file is easier to maintain, configure and enlarge, so in this case, you can advise it to use dynamics tables to transform this format:
PROJECT | Version | YEAR | JAN | FEB | MAR | APR | MAY | JUN | JUL | AUG | SEP | OCT | NOV | DEC | Total |
PRJ01 | Budget | 2013 | 1 | 2 | 3 | 5 | 6 | 7 | 8 | 88 | 99 | 89 | 45 | 56 | 409 |
PRJ01 | Budget | 2014 | 2 | 3 | 4 | 6 | 7 | 8 | 9 | 89 |
In this Format:
PROJECT | VERSION | YEAR | MONTH | VALUE |
PRJ01 | Budget | 2013 | JAN | 1 |
PRJ01 | Budget | 2013 | FEB | 2 |
PRJ01 | Budget | 2013 | MAR | 3 |
PRJ01 | Budget | 2013 | APR | 4 |
Kind Regards.
Alejandro
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
12 | |
4 | |
2 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.