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
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
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?
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.
js:%external%.split('/')[2]+'.'+%external%.split('/')[0]+'.'+%external%.split('/')[1]
split will result in array: for 1/9/2018 -> 1, 9, 2018