Skip to Content
0

BPC - Using js to convert data to TIME dimension format

4 days ago

33

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Lia Quarisa 3 days ago
0

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

Share
10 |10000 characters needed characters left characters exceeded
Lia Quarisa 3 days ago
0

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?

Share
10 |10000 characters needed characters left characters exceeded
Vadim Kalinin 3 days ago
0

Easy:

js:%external%.split('/')[2]+'.'+('0'+%external%.split('/')[0]).slice(0,2)+'.'+('0'+%external%.split('/')[1]).slice(0,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.

Share
10 |10000 characters needed characters left characters exceeded
Vadim Kalinin 4 days ago
0

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

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Any issues?

0