TotalConnect Date Transformation

JBell

Hi Everyone,
Ive been trying to get to a solution on using a formula to transform a non-standard date dimension into a date dimension that Datorama will recognise and have failed miserably!

the date format in the stream is coming through as this:

3.1.21 - 3.1.21 Ive tried using LEFT, TRIM, EXTRACT and PARSEDATE functions in various ways and nesting but am getting nowhere on something that should be fairly simple! Im no coder so this might be a fairly basic question for this community, but does anybody have a pre-written formula i can use to transform this to a date format in the stream? thx

Sushant_Kamath

@JBell, I tried this formula and it seems to have worked with a test Excel file. 

I imported the date entry as "Text", and mapped it to "Custom Classification 1" dimension, type: "Text" using the following formula - 

FORMATDATE(PARSEDATE(csv['date'],'mm.dd.yy - mm.dd.yy'),'mm.dd.yy - mm.dd.yy')

Let me know if this is the solution you were looking for. 

Sushant

Nidene_Pillay

Hi @JBell
 
Hope all is going well and you are enjoying the enhancements to our new TotalConnect connector? 

You can also try using PARSEDATE(EXTRACT(csv['date'],'-',0),'d.M.yy') mapped to your 'Day' field in Datorama. 

This will allow your file to extract a section of the date and also parse it into an acceptable format within Datorama.

Let us know how you get on.

Thanks,
Nidene