6 Replies Latest reply: Aug 3, 2011 4:02 PM by kramelot RSS

    Substract Dates

      Hi Everybody.

       

      I´m new in Qlikview.

       

      I need help in order subtract two dates, i got this dates in two table fields call "Fecha de Apertura" and "Inicio de Afectación" the idea is to obtain something like "DD, hh:mm:ss" after the subtract, i use only  [Fecha Apertura]-[Inicio de Afectacion] but it doesn´t work, how can i do??

       

      Best Regards

        • Re: Substract Dates
          Neil Miller

          What are you getting when you subtract the dates? A decimal number?

           

          The result should be the number of days. To get it in days, minutes, etc... you will need to convert that yourself.

           

          Here is an example for days/hours/minutes: Floor(([Fecha Apertura]-[Inicio de Afectacion])) & 'd  ' & Floor(Fmod(([Fecha Apertura]-[Inicio de Afectacion]), 1) * 24) & 'h  ' & Round(Fmod(Fmod(([Fecha Apertura]-[Inicio de Afectacion]), 1) * 24, 1) * 60) & 'm'

           

          It should return in the format: 4d 3h 14m

          • Re: Substract Dates

            Hi Kramelot, use something like this:

             

            tmp:
            LOAD * INLINE [
                FechaApertura, InicioAfectación
                05/01/2011 12:25:00, 01/01/2011 10:15:50
            ];
            
            data:
            load 
            interval(FechaApertura-InicioAfectación,'DD, hh:mm:ss') as SubstractDate
            resident tmp;
            

             

            Hope this help

              • Re: Substract Dates

                Hi.

                 

                I tried your suggestion but it doesn´t work.

                 

                Thanks

                  • Re: Substract Dates
                    John Witherspoon

                    Your dates are not dates or timestamps.  They are text fields.  You can't subtract text fields.

                     

                    You can probably read them in as timestamps by changing your TimestampFormat to match:

                     

                    SET TimestampFormat='DD/MM/YYYY hh:mm:ss';

                     

                    But I can't test because you're loading from a file you didn't attach. 

                     

                    You can't put a load with no source after the main load and expect anything to happen.  That sort of expression needs to be in the main load.

                     

                    interval([Fecha Apertura]-[Inicio de Afectacion|Inicio de la interrupcion de servicio],'DD, hh:mm:ss') as [Tiempo de Apertura],

                     

                    And that's assuming it interprets the timestamps correctly with the fixed format.  Otherwise it gets more complicated.