Best way to convert hours to decimal?

brianlees

• March 26, 2014 00:00

• Answered

Follow

We need to transform a field that contains a string hh:mm format to a decimal format. What is the quickest way to do that? I ripped through the manual several times to try and figure out how to build the formula. However, I've tried several different ways but I keep ending up with errors when I run the graph

Thanks,

Brian

• 

  imriskal

  • March 27, 2014 09:22

  0

  Comment actions Permalink

  Hi Brian,
  
  Could you please show a few example inputs and outputs? What exactly are you trying to achieve? Do you mean something like string "11:30" converted to decimal value 11.5? If so, here is a short example graph. I hope you will find it useful.
  decimals.grf
  Regards,

  • decimals.grf
• 

  brianlees

  • March 27, 2014 12:02

  0

  Comment actions Permalink

  That is exactly what we want to do. I downloaded the graph, but it does not open in Clover. I see the window flash like it is opening the file but then it doesn't open it. Am I doing it wrong? (Sorry, I'm new to Clover…and I REALLY LIKE IT!)
• 

  imriskal

  • March 28, 2014 09:14

  0

  Comment actions Permalink

  Please copy+paste the graph into your project into graph directory and the input file into data-in directory. The graph should open after double-clicking on it in Navigator pane.
  
  I am sorry, I did not realize you have community version of Designer which does not contain DataGenerator component I used in the graph. Please download the modified version.
  decimals_modified.grftimes.csv
  Regards,

  • decimals_modified.grf
  • times.csv
• 

  brianlees

  • March 28, 2014 12:52

  0

  Comment actions Permalink

  OK, I see exactly what you did and that makes total sense now. However, I'm getting a syntax error and I'm not sure why. Here is my reformat. Obviously I'm missing something simple in the language context of how to do this.
  
  function integer transform() {
  $out.0.EEID = $in.0.EEID;
  $out.0.Name = $in.0.Name;
  $out.0.Pay_Code = $in.0.Pay_Code;
  
  string[] splitted = split($in.0.hours,":");
  decimal hours = str2decimal(splitted[0]);
  decimal minutes = str2decimal(splitted[1]);
  
  minutes = minutes / 60;
  
  $out.0.hours = hours + minutes;
  $out.0.Assignment = $in.0.Account;
  
  return ALL;
  }
• 

  imriskal

  • March 28, 2014 14:32

  0

  Comment actions Permalink

  Could you please post the error you are getting?
  
  Is it possible that you have null values somewhere in your data? The example is not ready for null values so if you have any null in your input file, you should add an if statement handling null values.
  
  Regards,
• 

  brianlees

  • March 28, 2014 15:29

  0

  Comment actions Permalink

  I figured out the error and the graph executes without an error now. However, the output is not exactly right. It is outputting the decimal hours field as "yyyy-MM-dd40"
  
  Here is the transform
  
  

  function integer transform() {
  	$out.0.EEID = $in.0.EEID;
  
  	string[] accountconvert = split($in.0.Account,"\\/");
  	$out.0.Assignment = accountconvert[5];
  
  	string[] hoursconvert = split($in.0.Hours,":");
  	decimal hours = str2decimal(hoursconvert[0]);
  	decimal minutes = str2decimal(hoursconvert[1]);
  	minutes = minutes / 60;
  	$out.0.Hours = hours + minutes;
  
  	return ALL;
  }

  
  
  
  And here is the output:
  
  

  EEID,Assignment_Number,Hours_Worked
  124087,168696,yyyy-MM-dd40

• 

  brianlees

  • March 28, 2014 15:37

  0

  Comment actions Permalink

  I figured it out. I was formatting the output in the metadata. Ugh…missed that from a test earlier.

Please sign in to leave a comment.