Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
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;
Hi Sunny,
Thank You very much ... your help is much appreciated.
Regards,
Suman