Calculated Field using Timestamp Field

Bell Frazier shared this problem 13 months ago
Defect Logged

How can I get the data types to be compatible?


I am trying to using the calculated field:


Case when Intake Date <= rev_timein THEN Min(rev_timein) ELSE ‘0000-00-00 00:00:00’


But I keep getting an error that the data types aren’t compatible. Is there anyway to do this or to show NULL?

Replies (7)

photo
1

Hi Bell,

Thanks for your question.

You can't compare a date field with a timestamp field.

Have you set up the fields to be of the same type in the view and/or the report?

You can find this in the field settings screen in the view or the column formatting for the field in the report.

69a3f75df23df53970c0f2e5a04c2740

Let me know if that helps resolve your problem.

Kind regards,

Chris

photo
1

What would be the correct format to compare timestamp to timestamp?

photo
1

Hi Bell,

I did some experimentation with this just now and I think was able to reproduce your issue:

767759f2d0420ff4448e048d83eea68b

This seems to happen only when you include the ELSE statement with a timestamp value. As your desired outcome is a null anyway, you could just leave the ELSE and instead END it, as that will leave null values where the condition isn't matched.

I'm going to write this up as a bug and see what the developer team thinks.

Kind regards,

Chris

photo
1

Ok thanks. I need the else statement for if the timestamp is not less than or equal to

photo
2

Hi Bell,

The value 0000-00-00 00:00:00 wouldn't be valid anyway, as timestamps have to start from 1970. But it also doesn't work with that either, at the moment.

Try finishing the statement with simply ELSE END and let me know if that works for you.

Kind regards,

Chris

photo
1

Else End gives me a Validation Failed error message: ELSE clause is empty. Please enter a value in the ELSE clause and validate again.

photo
1

Hi Bell,

Which version of Yellowfin are you using? I have the ticket down as 7.1, but that's very old and out of support. I tested this in 9.8.

Kind regards,

Chris

photo
1

My apologies. We are using 8.0.4

photo
1

Hi Bell,

Thanks for letting me know. We have a bug logged for the defect of not being able to specify the value of the timestamp, so I'll move this ticket to defect logged, if that's everything. It seems that the support for using ELSE END only came in for the later versions of Yellowfin 9.

Kind regards,

Chris

photo
Leave a Comment
 
Attach a file