Text to Date conversion

sandrajcp

Hi All,

 

My import has dates in following format: "01-sep-20" and I want to convert it to monthly date "Sep 20". I was trying combinations of PERIOD, DATE, VALUE:

 

PERIOD(DATE(VALUE(LEFT('DATE (txt)', 2), MID('DATE (txt)', 4, 3), VALUE(RIGHT('DATE (txt)', 2))

 

But it seems that it won't work because it doesn't recognize "sep". Is there some function that would translate "sep" to 09?

 

Thanks a lot!

Sandra

ChrisAHeathcote

The text string "sep" has no meaning other than being a three character text string. If you are unable to define the text as a date within the import then you need to create a mapping that will convert the period text strings into a meaningful variable. 

Create a list containing the periods as per the text in your import.

Create a mapping table dimensioned by this new list and assign Jan = 1, Feb = 2 etc

You will now be able to use YEAR, MONTH, DAY to build you date format and PERIOD to convert to into a period format using the mapping table to create the number feeding the MONTH function. 

Putting all these together you should be able to convert your text into a viable period format. 

rob_marshall

@sandrajcp ,

 

Are saying your data is at the daily level but you only need it at the monthly level?  If so, import the data into a module dimensionalized by time and have Anaplan do the aggregation for you by using the Time mapping of DD-MM-YYYY.

 

If you are already importing the data at the daily level, then you can use a SYS Time module to get the parents of the day (week, month, quarter, half year, year).

 

If it is just a line item within your data set, do the above and do a lookup to the SYS Time - Daily module.

 

 

 

jasonblinn

You should just be able to import the data correctly using a custom time mapping,

 

Here is my data file, using the same naming convention you highlighted. 

 

 

Then once I have initiated the import, I click Custom Mapping, to map the import to a date formatted line item. 

 

Then a Mapping box pops up which allows you to pick the format of the data that is coming in. From there, select the top option D-M-Y (Screenshot shows the incorrect one) and on the right side, you can see a preview to show that it is working properly.

 

 

This will be cleaner, smaller, and more efficient since you don't have to use the Text line item (Date is 4 bytes, Text is 8), you can eliminate all staging lines, and can also cut out any computation required to calculate the date. 

 

Jason

 

ChrisAHeathcote

@sandrajcp 

I must admit that while the solution I provided is an option I would try and ensure that your convert the text to date during the import process and use a custom date mapping as suggested by @jasonblinn .

 

This is the best solution for all the reasons pointed out in the very comprehensive and detailed reply.