Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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!