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!
Page 1 / 1
Substring would work if the field were a string field and take the last 4 characters.
If it is a Date, then possible Case Expression:
If Date is less than 12/31/2018 and greater than or equal to 1/1/2018 then output number is 2018, and so on for each year.
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.
As John mentioned, the secret is a case expression:
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?
John - I was referring to the Transform task in Rules - just mapping from one field that is a date to a target that is a text
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?
This may be handy only if the data range is just a handful of years.
Also, if such rule has to be scheduled, it needs revision to CASE every year.
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.
