Text to Date conversion

Highlighted
New Contributor

Text to Date conversion

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

4 REPLIES 4
Highlighted
Master Anaplanner/Community Boss

Re: Text to Date conversion

@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.

 

2020-09-24_08-53-57.png

 

2020-09-24_08-54-25.png

 

Highlighted
Super Contributor

Re: Text to Date conversion

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. 

Highlighted
Certified Master Anaplanner

Re: Text to Date conversion

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. 

Date_Import.jpg

 

 

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

Anaplan_-_Jason_Sandbox_-_DEV.jpg

 

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.

Anaplan_-_Jason_Sandbox_-_DEV.jpg

 

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

Highlighted
Super Contributor

Re: Text to Date conversion

@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.