cancel
Showing results for 
Search instead for 
Did you mean: 

Transformation file with MVAL for fiscal year

manohar_pappireddy
Participant
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

former_member186338
Active Contributor
0 Kudos

Can you explain the flat file format you have to use for data import! Number of column? Month's position...

manohar_pappireddy
Participant
0 Kudos

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

former_member186338
Active Contributor
0 Kudos

Not clear:

"from column 12 to 23 are months signed data." - always 12 months? Always starting with 11 (December) month?

Vadim

manohar_pappireddy
Participant
0 Kudos


Hi Vadim,

Always 12 months data.Starting from Nov.

If a record is for 2015 year..the data is From Nov 2014 to Oct 2015.

Thanks,

Manohar

former_member186338
Active Contributor
0 Kudos

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

gajendra_moond
Contributor
0 Kudos

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.

former_member186338
Active Contributor
0 Kudos

CHK is not required, JavaScript will do the job. And JavaScript have to be used in any case different from multiple lines (one conversion file line per each TIME member).

Vadim

former_member186338
Active Contributor
0 Kudos

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)

gajendra_moond
Contributor
0 Kudos

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:

former_member186338
Active Contributor
0 Kudos

First - conversion file will require Javascript anyway. And on your sample month will be incorrect.

Second - look on the correct Javascript from my previous message - it will work without any prefixes.

former_member186338
Active Contributor
0 Kudos

First - conversion file will require Javascript anyway. And on your sample month will be incorrect.

Second - look on the correct Javascript from my previous message - it will work without any prefixes.

gajendra_moond
Contributor
0 Kudos

"And on your sample month will be incorrect." - I believe you are referring to your script against values ending "CHK"!

My script returned expected values:

manohar_pappireddy
Participant
0 Kudos


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

former_member186338
Active Contributor
0 Kudos

But absolutely not elegant

manohar_pappireddy
Participant
0 Kudos

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)

former_member186338
Active Contributor
0 Kudos

"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"!

Answers (1)

Answers (1)

gajendra_moond
Contributor
0 Kudos

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.