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

    load expression in script



      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

          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:



          LOAD * INLINE [


          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





          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;



          LOAD *,

          DATETIME_A - DATETIME_B as Duration,

          ' ' as Junk2

          Resident test2;


          drop table test2;



          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!