How can I compare dates between different fields to find the latest date?

@andreammelde thank you for sharing this use-case here. Me too interested to know this. I will try to find out from my end as well.

My thought in Data Designer is to do a transform where I do Plan Modified Date - Timeline Modified Date to get positive or negative, then to do another transform with a case where if date difference is positive or negative, to select the latest date. I would have to do that comparison a couple times to compare all 3 fields

@sai_ram Tried my workaround, but the Case formula does not allow to use Date as a datatype. 

I posted here about being able to use the date type in Case formula in order to do this

@andreammelde I was thinking that you could use a transformation step with a case expression to compare the dates, but doesn’t look like that’s possible.  My next plan would be to create a new field called Last Modified Date Overall, and then, use a rule to compare the dates, and populate based on the max date of all of them maybe by using a date diff formula to simplify.  (Just saw your other idea post come through, so looks like we were thinking the same. )

You can build a formula in Data designer with a number data type. In this choose Function. Inside the formula builder you get date diff.

Do that for every combination you have. Once you have that done do another transformation where you do case statements to determine the highest/lowest one outputting the proper date field.

Edit: ran into same problem of not being able to output a date.

Alternative solution, Union the date fields from all three places and take a max of them.

@Wayne trying to do the Union, but have not done much work with Union. When I am doing the merge, how should I do the setup? I want to have all the dates in one column, and then the Context ID/ GSID in one as well

Yup that’s right.

Once you have it unioned you will then do a Max Date by Relationship/Co gsid. Once you have that done you then remerge the final union set with the max date by relationship using the rel/co gsid and max date fields as your merge fields.

Assuming you are needing the gsid from the source.

@Wayne  have your received an error like below before? The dataset is failing. Assuming this is due to incompatible datatypes. 

Data Integration Error: SYSTEM ERROR: DrillRuntimeException: Join only supports implicit casts between 1. Numeric data 2. Varchar, Varbinary data 3. Date, Timestamp data Left type: INT, Right type: VARCHAR. Add explicit casts to avoid this error Fragment 0:0 [Error Id: 5e0cab45-1714-4653-9e93-a03d9c410809 on 10.0.161.236:31010]

Hi

we are evaluating if we can simplify tracking of success plan activity (activity on success plan, CTA or timeline) for the users. I will get back once I have enough information on it.

Hey @andreammelde I’m not sure why you are getting that error. Seems odd since all fields are date time fields in your screenshot.

Can you go through each union and merge and make sure all their data type icons are lining up.

We have changed this thread to feature request for better tracking.

The horizon rules engine Union option does not appear to work like proper sql. When trying to merge two datasets, each with one of the dates to compare, Gainsight’s Union requires you to pick which one to keep (the master), when I really want to merge the two datasets together in order to have a final transformation where I can pick the max date. When I tried not including the date field as part of the Union, but giving them the same display name, it gave an error that they couldn’t have the same name, when you give two different names, it creates two columns which is defeating the point of trying to union them. This solution is not producing the desired result. Please consider making the Union function work like it does in sql which would allow you to do this.