Measurement of a time lapse

[#1]

Hello Everyone,

So i'm trying to measure a duration between two steps.
Here's my code :

Load *,Round((Timestamp(Date(PREndDate)&' '&Time(PREndTime),'DD-MMM-YY hh:mm') - Timestamp(Date(PRStartDate)&' '&Time(PRStartTime),'DD-MMM-YY hh:mm'))*1440) as DurationPR;

Load *,Round((Timestamp(Date(DTEndDate)&' '&Time(DTEndTime),'DD-MMM-YY hh:mm') - Timestamp(Date(DTStartDate)&' '&Time(DTStartTime),'DD-MMM-YY hh:mm'))*1440) as DurationDT;

[Receipts]:

LOAD

BookRef,

     Batch,

     Skid,

     Cartons,

     PackType,

     [PR Logname],

     [PR Start Date] as PRStartDate,

     [PR Start Time] as PRStartTime,

     [PR End Date] as PREndDate,

     [PR End Time]as PREndTime,

     [DT Logname],

     [DT Start Date] as DTStartDate,

     [DT Start Time] as DTStartTime,

     [DT End Date] as DTEndDate,

     [DT End Time] as DTEndTime

FROM

[..\Downloads\New folder\Tierney\ALEX\ReceiptTimes.xls]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq, filters(

Remove(Row, Pos(Top, 1))

));

Both my duration Fields Are created after I reload the script but they are empty of values. The calculation is not made and I can't figure out why. Here is the excel file if you want to look at it.
I would be really grateful to any kind of suggestion.
Regards

Alexandre Bernard

[sunny_talwar]

Try this:

Load *,Round((Timestamp#(Date(PREndDate)&' '&Time(PREndTime),'DD-MMM-YY hh:mm') - Timestamp#(Date(PRStartDate)&' '&Time(PRStartTime),'DD-MMM-YY hh:mm'))*1440) as DurationPR;

Load *,Round((Timestamp#(Date(DTEndDate)&' '&Time(DTEndTime),'DD-MMM-YY hh:mm') - Timestamp#(Date(DTStartDate)&' '&Time(DTStartTime),'DD-MMM-YY hh:mm'))*1440) as DurationDT;

[sunny_talwar]

or this:

Load *,Round((Timestamp#(Date(PREndDate, 'DD-MMM-YY')&' '&Time(PREndTime, 'hh:mm'),'DD-MMM-YY hh:mm') - Timestamp#(Date(PRStartDate, 'DD-MMM-YY')&' '&Time(PRStartTime, 'hh:mm'),'DD-MMM-YY hh:mm'))*1440) as DurationPR;

Load *,Round((Timestamp#(Date(DTEndDate, 'DD-MMM-YY')&' '&Time(DTEndTime, 'hh:mm'),'DD-MMM-YY hh:mm') - Timestamp#(Date(DTStartDate, 'DD-MMM-YY')&' '&Time(DTStartTime, 'hh:mm'),'DD-MMM-YY hh:mm'))*1440) as DurationDT;

[sasiparupudi1 · Accepted solution]

load *,

  interval(Timestamp#(PREndDate &' '&PREndTime,'DD-MMM-YY hh:mm') - Timestamp#( PRStartDate &' '& PRStartTime ,'DD-MMM-YY hh:mm'),'s') as DurationPR,

    interval(Timestamp#(DTEndDate&' '&DTEndTime,'DD-MMM-YY hh:mm') - Timestamp#(DTStartDate&' '&DTStartTime ,'DD-MMM-YY hh:mm'),'s') as DurationDT;

LOAD

BookRef,

     Batch,

     Skid,

     Cartons,

     PackType,

     [PR Logname],

     date#([PR Start Date],'DD-MMM-YY') as PRStartDate,

     time#([PR Start Time],'hh:mm') as PRStartTime,

     date#([PR End Date],'DD-MMM-YY') as PREndDate,

     time#([PR End Time],'hh:mm') as PREndTime,

     [DT Logname],

     [DT Start Date] as DTStartDate,

     [DT Start Time] as DTStartTime,

     [DT End Date] as DTEndDate,

     [DT End Time] as DTEndTime

FROM

[ReceiptTimes.xls]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq, filters(

Remove(Row, Pos(Top, 1))

));

[maxgro]

LOAD

  *,

     Interval(

           (Date#([PR End Date], 'DD-MMM-YY') + Time#([PR End Time], 'hh:mm'))

           -

           (Date#([PR Start Date], 'DD-MMM-YY') + Time#([PR Start Time], 'hh:mm'))

     ) as DurationPR,

     Interval(

           (Date#([DT End Date], 'DD-MMM-YY') + Time#([DT End Time], 'hh:mm'))

           -

           (Date#([DT Start Date], 'DD-MMM-YY') + Time#([DT Start Time], 'hh:mm'))

     ) as DurationDT

FROM

ReceiptTimes.xls

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq, header is 1 lines);

[anlonghi2]

Hi Bernard,

please look at the script of the attached QV app that calculates PR & DT durations

Best regards

Andrea.

[MarcoWedel]

Hi,

maybe like this?

Receipts:
LOAD *,
    Interval([PR End Date]+[PR End Time]-[PR Start Date]-[PR Start Time]) as DurationPR,
    Interval([DT End Date]+[DT End Time]-[DT Start Date]-[DT Start Time]) as DurationDT;
LOAD BookRef,
    Batch,
    Skid,
    Cartons,
    PackType,
    [PR Logname],
    Date#([PR Start Date],'DD-MMM-YY') as [PR Start Date],
    [PR Start Time],
    Date#([PR End Date],'DD-MMM-YY') as [PR End Date],
    [PR End Time],
    [DT Logname],
    Date#([DT Start Date],'DD-MMM-YY') as [DT Start Date],
    [DT Start Time],
    Date#([DT End Date],'DD-MMM-YY') as [DT End Date],
    [DT End Time]
FROM [https://community.qlik.com/servlet/JiveServlet/download/835103-178527/ReceiptTimes.xls] (txt, codepage is 1252, embedded labels, delimiter is '\t', msq, header is 1 lines);

hope this helps

regards

Marco