on 07-27-2015 9:14 PM
Hi,
I have created a transformation file with the help of below thread for a calendar year.
http://scn.sap.com/thread/3361734
So if I get an entry for 2016,I can load for 2016.Jan to 2016.Dec.
But need to load the data from a flat file with multiple months of a fiscal year.
Our fiscal year is November to October.
So if I get an entry for 2016,need to load the data for 2015.11 to 2016.10.
Your help to create a transformation file for a fiscal year is highly appreciated.
Thanks,
Manohar
Can you explain the flat file format you have to use for data import! Number of column? Month's position...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Vadim,
We get the files in text file in below format.
ROLLFCST,BFC,USD,Prd.CC_DC_US_MRP,BA3044000D,Org.H8272,UNCONVERTED,AVERAGE,GEO_INPUT,USERINPUT,2016,10,20,30,40,50,60,70,80,90,100,10,10
To explain better with cloumn position:
Year is in column 11 and from column 12 to 23 are months signed data.
Expected result for one record:
I have created a transformation file in development system and the developmement box is not available due to maintenance.So I am not able to give you my current transforamation file.
With my current transforamation file,I am able to load the data for a calendar year.
Thanks,
Manohar
In the conversion file you can use JavaScript to perform conversion from Fiscal period to Calendar month ID.
Like you have Fiscal periods: 2016.01,2016.02,2016.03 etc
Then in conversion file you need to convert:
2016.01 -> 2015.11
2016.02 -> 2015.12
2016.03 -> 2016.01
...
In JavaScript read month part converting it to integer, if it's <=2 the subtract 1 from year and add 10 to month else don't change year and subtract 2 from month...
Vadim
Hi Manohar
Try something like this
*MVAL(*COL(12)|*COL(11)+*STR(.11)+*STR(CHK)||*COL(13)|*COL(11)+*STR(.12)+*STR(CHK)||*COL(14)|*COL(11)+*STR(.01)||*COL(15)|*COL(11)+*STR(.02)||*COL(16)|*COL(11)+*STR(.03)||*COL(17)|*COL(11)+*STR(.04)||*COL(18)|*COL(11)+*STR(.05)||*COL(19)|*COL(11)+*STR(.06)||*COL(20)|*COL(11)+*STR(.07)||*COL(21)|*COL(11)+*STR(.08)||*COL(22)|*COL(11)+*STR(.09)||*COL(23)|*COL(11)+*STR(.10))
You will have first 2 months with CHK string concatenated. You can use conversion file to adjust year for those two using javascript. I am not having access to the system right now but will try when I do.
Hi Vadim
I did not understand "(one conversion file line per each TIME member)". But this is what worked for me.
Transformation file:
TIME=
*MVAL(*COL(12)|*COL(11)+*STR(.11)+*STR(CHK)||*COL(13)|*COL(11)+*STR(.12)+*STR(CHK)||*COL(14)|*COL(11)+*STR(.01)||*COL(15)|*COL(11)+*STR(.02)||*COL(16)|*COL(11)+*STR(.03)||*COL(17)|*COL(11)+*STR(.04)||*COL(18)|*COL(11)+*STR(.05)||*COL(19)|*COL(11)+*STR(.06)||*COL(20)|*COL(11)+*STR(.07)||*COL(21)|*COL(11)+*STR(.08)||*COL(22)|*COL(11)+*STR(.09)||*COL(23)|*COL(11)+*STR(.10))
CONVERSION file:
Hi Vadim and Gajendra,
Thank you very much for the replies.I will work with your suggestions as soon as my system is available.Will update with the results.
But for time being the solution with conversion file
2016.01 -> 2015.11
2016.02 -> 2015.12
2016.03 -> 2016.01
seems simple and good for me..
Thanks,
Manohar
Hi Vadim/Gajendra,
Tried with Gajendra's solution.It is perfectly working for me.
Thank you very much.
But taking more time.Will try with Vadim's solution also.
Do we need to have this in conversion file?Looks very tough for me to understand.
Or another algorithm using months calculation:
js: ((parseInt(%external%.substr(0,4))*12+parseInt(%external%.substr(5,2))-3)/12^0)+"."+("0"+((parseInt(%external%.substr(0,4))*12+parseInt(%external%.substr(5,2))-3)%12+1)).slice(-2)
"Looks very tough for me to understand." - it's very simple:
^0 - truncate to integer
%12 - get a reminder after division by 12
etc...
Just put it in Internal...
Use any Online JavaScript editor like Online Javascript Editor
to test script.
P.S. Sorry, "Internal"!
Hi Manohar
You can use an End Routine BADI to perform this adjustment. Another way is to create a one time conversion file for say 20-30 years in the future - more or less depending on your requirement which will convert from calendar month to fiscal period.
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.