Converting a beastmode text field into a number field

Jbrorby

Hello,

 

I am struggling to get the values my beastmode returns to display in the correct format.

 

To summarize, I'm looking at blood draw times for donors.   The data comes into Domo as phlebotomy start time, and phlebotomy end time (which are both date fields).   So, I made the beastmode: TIMEDIFF(`lte_donation_phleb.stop_time`, `lte_donation_phleb.start_time`).   And since phlebotomies are always started and stopped on the same day, this is giving me hh:mm:ss, which is good.

 

The problem is that this ends up as a text field, and I need it to be a number field.  I was able to get the beastmode to be a number field, but now this is the problem I am having:

If the minutes difference between stop and start time are single digit, or if they are multiples of 10 (end in 0), it doesn't display correctly.

 

For example:

Draw time was 1 hour, 20 minutes (01:20) the beastmode returns 1.2

Draw time was 1 hour, 2 minutes (01:02) the beastmode returns 1.2

 

Ideally I'd like 1.20 and 1.02

 

But, if the difference were, say, 1 hour, 23 minutes, it displays correctly as 1.23

 

This is the beastmode:

 

ABS(CONCAT((HOUR(TIMEDIFF(`lte_donation_phleb.stop_time`, `lte_donation_phleb.start_time`))),'.',(MINUTE(TIMEDIFF(`lte_donation_phleb.stop_time`, `lte_donation_phleb.start_time`)))))

 

I tried inserting a time_format function inbetween the minute and timediff portion of the 2nd half of the concat, trying to make minutes display as mm, but I couldn't get it to work

 

 

 

 

GrantSmith

Try this:

 

CONCAT(
HOUR(TIMEDIFF(`lte_donation_phleb.stop_time`, `lte_donation_phleb.start_time`)),
  '.',
CASE WHEN MINUTE(TIMEDIFF(`lte_donation_phleb.stop_time`, `lte_donation_phleb.start_time`)) < 10 THEN 
   CONCAT('0', MINUTE(TIMEDIFF(`lte_donation_phleb.stop_time`, `lte_donation_phleb.start_time`)))
ELSE
   MINUTE(TIMEDIFF(`lte_donation_phleb.stop_time`, `lte_donation_phleb.start_time`))
END
)

It's checking to see if the minutes are less than 10, if so add a 0 to the minutes otherwise just use the minutes.

 

This is under the assumption that you're wanting to display this value as hours.minutes and not as fractions of hours. If it's fraction of hours then @MarkSnodgrass's answer should work for you instead.