Skip to Content
0

BPC - Using js to convert data to TIME dimension format

Apr 17 at 04:59 AM

77

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.

5 Answers

Best Answer
Vadim Kalinin Apr 18 at 07:31 AM
0

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.

Share
10 |10000 characters needed characters left characters exceeded
Lia Quarisa Apr 18 at 05:17 AM
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 Apr 18 at 05:11 AM
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 Apr 17 at 06:23 AM
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
Lia Quarisa Apr 24 at 04:41 PM
0

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

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

But why not to use my correct JS formula? It's the correct answer to your question!

Your formula in mapping is correct but hard to read :)

0

Hi Vadim, your JS formula doesn't seem to work. I uploaded data for each day in January and yet I only see data in Jan 1 - 9. Month and day fields could be 1 or 2 digits and i think your formula isn't taking that into consideration.

Example: Jan 10, 2018

External 1/10/2018 is mapping to Internal 2018.01.01 [this is what is happening]

It should be mapping to 2018.01.10

Thanks

Lia

0

Sorry, my mistake!

I will correct the answer:

slice (-2)

has to be used

0