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