Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
alvinford
Contributor III
Contributor III

How to calculate date difference between two steps

Hi All,

I have the following data referring Note 2. Would like to achieve the output referring to Note 1. Would like to calculate the Date difference between two steps and achieve the DateDiff column. Kindly refer the Note 3 for reference how output is achieved.

Please refer the attachment for Data sample.

Thank You in Advance..

Note 1:

   

ProcessStepDateDateDiff
TRP3/12/2012 15:220
HLD15/2/2013 10:5373.81
HLD COPY22/2/2013 10:046.96
TRF25/2/2013 10:489.99
CAT27/2/2013 13:592.13
CATL27/2/2013 14:052.13
PRS4/3/2013 14:034.99
HLD COPY12/3/2013 10:2812.84
PRSS12/3/2013 10:460

Note 2 :

    

NoDateProcessStepStep
643995G03/12/2012 3:22:48 PMTRP1
643995G03/12/2012 3:22:50 PMTRP1
643995G15/02/2013 10:53:57 AMHLD2
643995G15/02/2013 10:54:00 AMHLD COPY3
643995G20/02/2013 4:38:26 PMHLD COPY3
643995G21/02/2013 3:27:01 PMHLD COPY3
643995G22/02/2013 9:38:32 AMHLD COPY3
643995G22/02/2013 9:39:32 AMHLD COPY3
643995G22/02/2013 10:04:53 AMHLD COPY3
643995G22/02/2013 10:04:56 AMHLD COPY3
643995G25/02/2013 10:48:18 AMTRF4
643995G25/02/2013 10:48:19 AMCAT5
643995G27/02/2013 1:59:44 PMCAT5
643995G27/02/2013 1:59:45 PMCAT5

The output  columns as  achieved is as follows

 

Note 3: 

DateDiff is achieved as Process is achieved as Date is achieved
Min of Step 1ProcessStep of Step 1Min Date of Step 1
Max(Step 2)-  Min( Step1)ProcessStep of Step 2Max Date of Step 2
Max(Step3)-  Min( Step2)ProcessStep of Step 3Max Date of Step 3
Max(Step4)-  Min( Step3)ProcessStep of Step 4Max Date of Step 4
Max(Step5)-  Min( Step4)ProcessStep of Step 5Max Date of Step 5
Max(Step6)-  Min( Step5)ProcessStep of Step 6Max Date of Step 6
Max(Step7)-  Min( Step6)ProcessStep of Step 7Max Date of Step 7
Max(Step8)-  Min( Step7)ProcessStep of Step 8Max Date of Step 8
Max(Step9)-  Min( Step8)ProcessStep of Step 9Max Date of Step 9

regards,

Alvin

1 Solution

Accepted Solutions
sunny_talwar

Or this:

Table:

LOAD No,

    Date,

    ProcessStep,

    Step

FROM

DataSample.xls

(biff, embedded labels, table is Sheet1$);

NewTable:

LOAD No,

  Step,

  Min(Date) as MinDate,

  Max(Date) as MaxDate

Resident Table

Group By No, Step

Order By No, Step;

FinalTable:

LOAD No,

  Step,

// MinDate,

// MaxDate,

  Round(SubField(Interval(RangeSum(MaxDate, -Alt(Previous(MinDate), MaxDate)), 'D.hh'), '.', 1) +

  SubField(Interval(RangeSum(MaxDate, -Alt(Previous(MinDate), MaxDate)), 'D.hh'), '.', 2)/24, 0.01) as Diff

Resident NewTable;

DROP Table NewTable;


Capture.PNG

View solution in original post

3 Replies
sunny_talwar

Something like this:


Table:

LOAD No,

    Date,

    ProcessStep,

    Step

FROM

DataSample.xls

(biff, embedded labels, table is Sheet1$);

NewTable:

LOAD No,

  Step,

  Min(Date) as MinDate,

  Max(Date) as MaxDate

Resident Table

Group By No, Step

Order By No, Step;

FinalTable:

LOAD No,

  Step,

// MinDate,

// MaxDate,

  Interval(RangeSum(MaxDate, -Alt(Previous(MinDate), MaxDate)), 'D.hh') as Diff

Resident NewTable;

DROP Table NewTable;


Capture.PNG

sunny_talwar

Or this:

Table:

LOAD No,

    Date,

    ProcessStep,

    Step

FROM

DataSample.xls

(biff, embedded labels, table is Sheet1$);

NewTable:

LOAD No,

  Step,

  Min(Date) as MinDate,

  Max(Date) as MaxDate

Resident Table

Group By No, Step

Order By No, Step;

FinalTable:

LOAD No,

  Step,

// MinDate,

// MaxDate,

  Round(SubField(Interval(RangeSum(MaxDate, -Alt(Previous(MinDate), MaxDate)), 'D.hh'), '.', 1) +

  SubField(Interval(RangeSum(MaxDate, -Alt(Previous(MinDate), MaxDate)), 'D.hh'), '.', 2)/24, 0.01) as Diff

Resident NewTable;

DROP Table NewTable;


Capture.PNG

alvinford
Contributor III
Contributor III
Author

Hi Sunny,

Thank You very much ... your help is much appreciated.

Regards,

Suman