Skip to Content
avatar image
Lia Quarisa

BPC - Using js to convert data to TIME dimension format

Apr 17, 2018 at 04:59 AM | 88 Views

I am trying to create an expression for my conversion file that would map the external format in the flat file to the time dimension format as per example below:

External ===> Internal (BPC)

1/9/2018 ====> 2018.01.09

1/19/2018 ====> 2018.01.19

12/2/2018 ====> 2018.12.02

12/31/2018 ====> 2018.12.31

SAP Business Planning and Consolidation, version for SAP NetWeaver
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Best Answer
    avatar image
    Vadim Kalinin
    Apr 18, 2018 at 07:31 AM

    Easy:

    js:%external%.split('/')[2]+'.'+('0'+%external%.split('/')[0]).slice(-2)+'.'+('0'+%external%.split('/')[1]).slice(-2)

    P.S. JavaScript performance is fine for this job!

    P.P.S. Please try to do something yourself! For example - read any online JavaScript tutorial.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Vadim Kalinin
    Apr 17, 2018 at 06:23 AM

    js:%external%.split('/')[2]+'.'+%external%.split('/')[0]+'.'+%external%.split('/')[1]

    split will result in array: for 1/9/2018 -> 1, 9, 2018

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Lia Quarisa
    Apr 18, 2018 at 05:11 AM

    Thank you for the useful answer. Would it be possible to sort the external date format so it is in the correct order for the internal date format so have year.m.d with a 0 in front of month and day if it is single digit. Also I am wondering if performance is better or worse if i just have a conversion file with all possible dates over 4 years so around 1500?

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Lia Quarisa
    Apr 18, 2018 at 05:17 AM

    Many thanks for the quick and useful reply. Is there a way to reverse the order from m/d/yyyy to yyyy.m.d with a 0 if a month or day is single digit? Apologies I am new to js. Also I wonder if performance is better using a js expression such as this or whether to simply have a conversion file listing all the possible dates in External and the corresponding IDs for Internal. The file would have about 1500 rows to hold all days for 4 years. Thanks in advance

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Lia Quarisa
    Apr 24, 2018 at 04:41 PM

    Hi Vadim

    In the end, I decided to put a formula in the mapping section of the transformation file as follows:

    TIME = *IF(*COL(3,4:4) = *STR(/) THEN *COL(3,5:8) + *STR(.0) + *COL(3,1:1) + *STR(.0) + *COL(3,3:3);

    *COL(3,6:6) = *STR(/) THEN *COL(3,7:10) + *STR(.) + *COL(3,1:2) + *STR(.) + *COL(3,4:5);

    *COL(3,2:2) = *STR(/) THEN *COL(3,6:9) + *STR(.0) + *COL(3,1:1) +*STR(.)+*COL(3,3:4);

    *COL(3,6:9) + *STR(.) + *COL(3,1:2)+*STR(.0)+*COL(3,4:4))

    This covers all possible combinations in the flat file: m/d/yyyy, mm/dd/yyyy, m/dd/yyyy and mm/d/yyyy. They are all converted to yyyy.mm.dd

    Add comment
    10|10000 characters needed characters exceeded