6 Replies Latest reply: May 15, 2014 10:10 AM by davast _ RSS

    Change Number values back to Time values

    davast _

      Hello everyone. With the assistance of a Datawatch trainer, we have a model that transfers a time value (e.g. 08:30) to a fixed number value (8.30) and then uses it in the Summary Window to list all the various delivery windows across the days of the week.

       

      The formula used in Monarch is

      "Val(Left(,2))+(Val(Right(,2))/100)"

      When we export this to xlsx file it is then sent on to our customers to check & validate against their records.

       

      How do we change our xlsx file back into Time format so that our customers can carry out a comparison with there records that are in Time format?

       

      Hope I have explained myself.

        • Change Number values back to Time values
          Olly Bond

          Hello

           

          It sounds like the original data is a character field, so have a look at the ctod function.

           

          Best wishes

           

          Olly

            • Change Number values back to Time values
              davast _

              Thanks for the swift reply Olly. As far as I can see from looking at ctod is that it works in the report & table window, whilst my information detail is in the summary window & is in numeric format ready for extracting to an excel file. It has to be in numeric format to make it into the summary window.

                • Change Number values back to Time values
                  Olly Bond

                  Hello

                   

                  It only needs to be numeric if you want to use it as a measure in a summary.

                   

                  You can use dates/times, and indeed character fields, as either key fields or as item fields in summaries.

                   

                  Hope this helps,

                   

                  Olly

                    • Change Number values back to Time values
                      davast _

                      That's the problem I have. The original table lists all the individual delivery drops for all customers with the window start (win_start) & window end (win_end) times in time format hh:mm. To convert them to a summary table that shows the days of the week across the top & the individual customers down the summary, we had to make the window time into a numeric format to be measured Sum(win_start) & Sum(win_end).

                        • Change Number values back to Time values
                          Grant Perkins

                          It seems to me that you need to send the figures out as they were but then have the revised display using the numeric conversion for presentation.

                           

                          It should be feasible to include the original values as they were within the export - either visible or 'hidden' - in a way that the customers can get at it.  I suspect that is what Olly is suggesting.

                           

                          It occurs to me that the aggregated (?) summary data may not be as useful to the customers as the original raw table data would be when they are trying to compare things. In which case maybe the export you distribute would benefit from having both a summary and a table export. (Table with a reduced number of fields published I would assume.)

                           

                          Of course if the customers also have Monarch available their comparison task might well be much easier ..... you could provide them with a suitable model as part of the service. 

                           

                          Grant