3 Replies Latest reply: Jul 11, 2012 8:55 AM by Stefan Wühl RSS

    calculate a datetime difference

    Beat Roos

      Hi

       

      I do have the following Table:

       

      DateTimeFieldoldValuenewValue
      01.07.2012 - 08.04created
      01.07.2012 - 08.15statusopeninProgress
      02.07.2012 - 10.15OwnerUser1User2
      03.07.2012 - 14.50statusinProgressclosed

       

      now I need to calculate the total time needed for the Document. I need no know the difference from the create till the status = closed. There is only one DateTime in that history table and also other status which I do not need to calculate. Just the difference from beginning to the end.

       

      How can I solve that? Can anybody help me to solve that?

       

      Thanks a lot!

        • Re: calculate a datetime difference
          Stefan Wühl

          Maybe something like this?

           

          LOAD

          DocumentID,

          interval(only(if(Field='status' and newValue='closed',DateTime)) - only(if(Field='created', DateTime)),'hh:mm') as DateTimeDiff

          resident YOURTABLE group by DocumentID;

            • Re: calculate a datetime difference
              Beat Roos

              Thank you swuehl

               

              It works not like this. I get no result on each line and it creates a new Table "Tabelname -1".

              I do not understand it really...

               

              The original Table is CaseHistory. There I do have an ID (%CaseID) to a second Table, calling Case.

              I do get the History Values per Case inside that History Table.

              btw I did load the Tables from a qvd File and use qualify and unqualify % to not generate syntetic keys.

               

              Is it easier to calculate the date-diff for each step? But how can QV now the right sort order, how to tell QV which is the first entry, the second and so on?

               

              Thanks a lot for helping me.

               

              My code is:

               

               

              LOAD
              %CaseID,
              interval(only(if(CaseHistory.Field='status' and CaseHistory.NewValue='closed',CaseHistory.CreatedDate))
              -
              only(if(CaseHistory.Field='created', CaseHistory.CreatedDate)),'hh:mm') as DateTimeDiff
              resident CaseHistory group by %CaseID

              ;

               

               

               

                • Re: calculate a datetime difference
                  Stefan Wühl

                  I still think the approach should work.

                   

                  You need to take care that your DateTime is interpreted correctly as Timestamp, e.g. by using an appropriate standard format.

                   

                  You table is called "Tabelname -1", because we haven't set a specific different name, so QV just follow the name pattern of the previous table. We can correct this by explicitely stating a table name:

                   

                  SET TimestampFormat='DD.MM.YYYY - hh.mm';

                   

                  QUALIFY *;

                  UnQualify %CaseID;

                   

                  CaseHistory:

                  LOAD * INLINE [

                  %CaseID, DateTime,    Field,    oldValue,    newValue

                  C0815,01.07.2012 - 08.04,    created,        ,

                  C0815,01.07.2012 - 08.15,    status,    open,    inProgress

                  C0815,02.07.2012 - 10.15,    Owner,    User1,    User2

                  C0815,03.07.2012 - 14.50,    status,    inProgress,    closed

                  ];

                   

                  CaseTimeDiff:

                  LOAD

                  %CaseID,

                  interval(only(if(CaseHistory.Field='status' and CaseHistory.newValue='closed',CaseHistory.DateTime))

                  - only(if(CaseHistory.Field='created', CaseHistory.DateTime)),'hh:mm') as DateTimeDiff

                  resident CaseHistory group by %CaseID

                  ;