Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

alvinford
Not applicable

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

Tags (1)
1 Solution

Accepted Solutions
sunny_talwar
Not applicable

Re: How to calculate date difference between two steps

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

3 Replies
sunny_talwar
Not applicable

Re: How to calculate date difference between two steps

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
Not applicable

Re: How to calculate date difference between two steps

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
Not applicable

Re: How to calculate date difference between two steps

Hi Sunny,

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

Regards,

Suman