2 Replies Latest reply: Apr 25, 2012 8:35 AM by Hilaire GODINOT RSS

    Subtracting dates

      Hi,

      In the attached QVW for the field "DeliveryDate" I want to subtract the minimum date from the maximum date for a given container number.

      It doesn't work. I'm pretty sure it is because of the date format. Any suggestions what I'm doing wrong? Source html files attached as well.

      BR. Jakob

        • Re: Subtracting dates
          Christophe Brault

          hi,

           

          Yes, your problem is because of date format.

           

          See attachef file

           

          if you have a PE edition :

           

          Script :

           

          Containers:

          LOAD [Contnr ID],

               SubField( Deliv.Date,'.',3)&'-'&SubField(Deliv.Date,'.',2)&'-'&SubField( Deliv.Date,'.',1) as DeliveryDate,

               Containers,

               left(SubField(FileName(),'-',3),4)&'-'&SubField(FileName(),'-',2)&'-'&right(SubField(FileName(),'-',1),2) as ExtractDate

          FROM

          [D:\Bureautique\Downloads\Cont*.htm]   // Change with your path

          (html, codepage is 1252, embedded labels, table is @1);

           

           

          expression in graph :

           

          MinDate:

          date(min(DeliveryDate))

          MaxDate:

          date(max(DeliveryDate))

          Difference :

          MaxDate-MinDate

          • Re: Subtracting dates

            Hi,

             

            you just have to change your loading script as follows to match the string date format

             

            LOAD [Contnr ID],

                  date(date#(Deliv.Date,'DD.MM.YYYY')) as DeliveryDate,

                  //Containers

                 Today() as ExtractDate

            FROM

            .....