Acknowledged

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

Votes
andreammelde
Rank
Userlevel 7
Badge +2

I have created a data designer dataset on Success Plans with 3 date fields: Plan Last Modified Date, Associated CTA last Modified Date, and Timeline Last Activity Date. I need to compare between the 3 fields to find the MAX date of the three columns. The idea is that this is the true “last modification” date of the plan, as we consider modifying a CTA or Timeline to also be working a plan.

 

Has anyone had success comparing dates between columns? I have the max date within the column itself figured, now need to compare between the 3

 

 

Andrea Carroll

12 replies

sai_ram
Rank
Userlevel 7
Badge +2

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

 

andreammelde
Rank
Userlevel 7
Badge +2

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

Andrea Carroll
andreammelde
Rank
Userlevel 7
Badge +2

@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

Andrea Carroll
heather_hansen
Rank
Userlevel 7
Badge +5

@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. :grin: )

W
Rank
Userlevel 5

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.

andreammelde
Rank
Userlevel 7
Badge +2

@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

 

Andrea Carroll
W
Rank
Userlevel 5

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.

andreammelde
Rank
Userlevel 7
Badge +2

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

Andrea Carroll
andreammelde
Rank
Userlevel 7
Badge +2

 

Andrea Carroll
Anil Raj Pujari
Rank
Userlevel 5

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.

W
Rank
Userlevel 5

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.

Anil Raj Pujari
Rank
Userlevel 5

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

Reply


Terms & ConditionsCookie settings