3 Replies Latest reply: Sep 16, 2012 6:18 PM by barry_hayward RSS

    loading calculated fields from QVD

      I have a script that loads from a QVD file, but I want to add fields into that table load, using a calcualtion on fields in the QVD.

      I have tied the following but the load crashes.

       

       

      Directory

           Jobref,

      ;
      LOAD

      Can anyone help point me in the right direction? Im very new to load scripts!!

      Barry

           DateRaised

           Num((now()-DateRaised),0) as DaysSinceRaised

      From

      Job.qvd

      (qvd);

        • Re: loading calculated fields from QVD

          sorry it seems to have messed up the text...it should be: -

          I have a script that loads from a QVD file, but I want to add fields into that table load, using a calcualtion on fields in the QVD.

          I have tied the following but the load crashes.

           

          Directory;

          LOAD

               DateRaised

               Num((now()-DateRaised),0) as DaysSinceRaised

          From

          Job.qvd

          (qvd);

           

          Can anyone help point me in the right direction? Im very new to load scripts!!

          Barry

            • Re: loading calculated fields from QVD
              Stefan Wühl

              There is a comma missing after DateRaised to separate the fields loaded.

              Then, the second argument to Num() function should be a string, representing the format code.

               

              But if you want to truncate the time interval you're calculating to whole days, try using round(), floor() or ceil() function (num will only change the display of the value, not the internal storage).

               

              So maybe like this:

               

              Directory;

              LOAD

                   DateRaised,

                   round(now()-DateRaised) as DaysSinceRaised

              From

              Job.qvd

              (qvd);

               

              or

                   Num((Now()-DateRaised),'0') as DaysSinceRaised

               

              (or whatever format code is appropriate)

               

              Hope this helps,

              Stefan

                • Re: loading calculated fields from QVD

                  Thank you for the function help. I have changed it to

                  Directory;

                  LOAD

                       JobRef,
                      
                  round(now()-DateRaised) as DaysSinceRaised,
                      
                  round(networkdays(DateRaised,now())) as DaysNetWorkSinceRaised    
                  FROM
                  Jobs.qvd
                  (
                  qvd);

                   

                  but it does not reload. it hangs at 01 secs. It it fine if I take out the two function lines!

                  Barry