I am looking for a way to strip just the year out of a date when performing a Transformation task in a query builder. i.e. If the date is 1/1/2014 I'd like the transform task to output "2014".
The reasoning is a cool holiday e-card for our large, long standing customers. We want to show them where they were when they started (seat count, etc.), their growth over that time (current - initial seats), along with the billboard top song, top grossing movie, and Apple released in that year (were an Apple software company).
Any thoughts?
Thanks!
Best answer by ankit_jain
If the objective is to send e-cards, I am hoping that you are using Journey Orchestrator and Email. In the Email Templates, you can map Show Fields of data type DATE and format values. One such option JO offers to format a date to just YYYY.
Ben _ I have not tried this yet - and it is an extra step - but adding to John's comment - do a task to map the date field to a text field in your output file and then use the substring function.
I still think this would be a good add to better allow for YOY review of data. Expanding this to include both year and month from a date. This would allow you to group a metrics in reports by year and month to compare, for example, November 2018 to November 2017.
Hey Denise - could you provide more detail on "map the date field to a text field in your output file"? Do you mean within Journey Orchestrator Participant Mapping or a transformation task within the query? Or something else?
Hi Denise - I'm struggling to figure out the formula field that would be used for changing a date to a string. I have a similar to need to change a string value to a numeric value, is this possible?
If the objective is to send e-cards, I am hoping that you are using Journey Orchestrator and Email. In the Email Templates, you can map Show Fields of data type DATE and format values. One such option JO offers to format a date to just YYYY.
If you ever had a profile with us, there's no need to create another one. Don't worry if your email address has since changed, or you can't remember your login, just let us know at community@gainsight.com and we'll help you get started from where you left.
Else, please continue with the registration below.
+5
+5
