1 Reply Latest reply: Mar 13, 2012 3:01 PM by Brandon Apperson RSS

    load expression in script

      hello

       

      i've a problem. In my board i have two fields, booth are date/time field like 01-01-2012 08:00 fits date/time is the startdate and the other the finishdate.

       

      In some tables i used the expression finishdate - startdate to calculate the duration, no problem with that.

       

      But nw i want this expression to be loaded in the script, so that a new field ak duration ia avalable. When this new field is avalable i can make a statistic object

       

      , which displays the maximum/mimimus/averige duration.

       

      Anyone suggestions

       

      grzt Ed

        • Re: load expression in script
          Brandon Apperson

          Okay so to solve this you need to use the timestamp function as well as the interval function:

          Since I don't have your actual data I made an inline table.

          The script:

           

          test:

          LOAD * INLINE [

          CUSTOMERNBR, ARTNBR, YEAR, SALES, DATETIME1, DATETIME2

          5436698, 3154, 2011, 317000, 01-01-2012 08:00, 01-01-2012 02:00

          5436698, 3154, 2010, 325000, 01-02-2011 07:00, 01-01-2011 07:00

          5436698, 3169, 2011, 100000, 03-01-2010 02:00, 02-01-2010 02:00

          5436698, 3169, 2010, 150000, 02-01-2013 10:00, 01-01-2013 10:00

          ]

          ;

           

          test2:

          LOAD *,

          timestamp#(DATETIME1,'MM-DD-YYYY hh:mm') as DATETIME_A,

          timestamp#(DATETIME2,'MM-DD-YYYY hh:mm') as DATETIME_B,

          '' as Junk

          Resident test;

           

          drop table test;

           

          test3:

          LOAD *,

          DATETIME_A - DATETIME_B as Duration,

          ' ' as Junk2

          Resident test2;

           

          drop table test2;

           

          test4:

          LOAD *,

          interval(Duration, 'MM-DD-YYYY hh.mm') as Difference,

          ' ' as Junk3

          Resident test3;

           

           

          Drop table test3;

           

          I am subtracting DATETIME1-DATETIME2 for your results.

           

          You can also change the format of the difference by changing the MM-DD-YYYY hh.mm part.

           

          I also attached a QV file let me know if you need any other help.

           

          Hope this helps!