topic Re: How to calculate date difference between two steps in QlikView

How to calculate date difference between two steps

alvinford — Wed, 20 Apr 2016 14:49:29 GMT

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:

ProcessStep	Date	DateDiff
TRP	3/12/2012 15:22	0
HLD	15/2/2013 10:53	73.81
HLD COPY	22/2/2013 10:04	6.96
TRF	25/2/2013 10:48	9.99
CAT	27/2/2013 13:59	2.13
CATL	27/2/2013 14:05	2.13
PRS	4/3/2013 14:03	4.99
HLD COPY	12/3/2013 10:28	12.84
PRSS	12/3/2013 10:46	0

Note 2 :

No	Date	ProcessStep	Step
643995G	03/12/2012 3:22:48 PM	TRP	1
643995G	03/12/2012 3:22:50 PM	TRP	1
643995G	15/02/2013 10:53:57 AM	HLD	2
643995G	15/02/2013 10:54:00 AM	HLD COPY	3
643995G	20/02/2013 4:38:26 PM	HLD COPY	3
643995G	21/02/2013 3:27:01 PM	HLD COPY	3
643995G	22/02/2013 9:38:32 AM	HLD COPY	3
643995G	22/02/2013 9:39:32 AM	HLD COPY	3
643995G	22/02/2013 10:04:53 AM	HLD COPY	3
643995G	22/02/2013 10:04:56 AM	HLD COPY	3
643995G	25/02/2013 10:48:18 AM	TRF	4
643995G	25/02/2013 10:48:19 AM	CAT	5
643995G	27/02/2013 1:59:44 PM	CAT	5
643995G	27/02/2013 1:59:45 PM	CAT	5

The output columns as achieved is as follows

Note 3:

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

regards,

Alvin

Re: How to calculate date difference between two steps

sunny_talwar — Wed, 20 Apr 2016 15:02:25 GMT

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;

Re: How to calculate date difference between two steps

sunny_talwar — Wed, 20 Apr 2016 15:05:53 GMT

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;

Re: How to calculate date difference between two steps

alvinford — Thu, 21 Apr 2016 23:43:54 GMT

Hi Sunny,

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

Regards,

Suman