Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
If any of the date and time field is null then picking time shud be blank...
May be this
Table:
LOAD *,
RangeSum([Confirmation date first step], [Confirmation time first step]) as [Confirmation datetime first step],
RangeSum([Confirmation date], [Confirmation time]) as [Confirmation datetime],
AutoNumber(RowNo(), [TO Nr.]) as Rank_New,
If(Len(Trim([Confirmation date first step])) = 0, 1, 0) as NullFlag;
LOAD * INLINE [
TO Nr., TO item, Material, Confirmation date first step, Confirmation time first step, Rank, Confirmation date, Confirmation time
155214, 1, 422203700110, 22-2-2018, 13:19:00, 1, 22-2-2018, 13:19:39
155214, 2, 423509910411, 22-2-2018, 13:20:53, 2, 22-2-2018, 14:09:05
155214, 3, 422200253991, 22-2-2018, 13:40:28, 3, 22-2-2018, 13:40:28
155214, 4, 422200253991, 22-2-2018, 13:42:47, 4, 22-2-2018, 14:09:05
155214, 5, 422203426171, 22-2-2018, 13:47:25, 5, 22-2-2018, 14:09:05
155214, 6, 422203534323, 22-2-2018, 14:08:53, 6, 22-2-2018, 14:09:05
155214, 7, 422203700110, 22-2-2018, 14:09:57, 7, 22-2-2018, 14:10:58
155214, 8, 422200253991, 22-2-2018, 14:10:45, 8, 22-2-2018, 14:10:45
155214, 9, 422200253991, , , 9, 22-2-2018, 14:10:45
];
FinalTable:
LOAD *,
Interval(If(NullFlag = 0,
If([Confirmation datetime] = Previous([Confirmation datetime]), Previous([Confirmation datetime first step]) - [Confirmation datetime first step], [Confirmation datetime] - [Confirmation datetime first step]))) as [Picking Time]
Resident Table
Order By [TO Nr.], [Confirmation datetime first step] desc;
DROP Table Table;
It would be helpful if you can provide the sample data as well.... so that we can test it out
If any of the date and time files is empty then picking time shud be blank
TO Nr. | TO item | Material | Confirmation date first step | Confirmation time first step | Rank | Confirmation date | Confirmation time | Picking time |
155214 | 1 | 422203700110 | 22-2-2018 | 13:19:00 | 1 | 22-2-2018 | 13:19:39 | 0:00:39 |
155214 | 2 | 423509910411 | 22-2-2018 | 13:20:53 | 2 | 22-2-2018 | 14:09:05 | 0:48:12 |
155214 | 3 | 422200253991 | 22-2-2018 | 13:40:28 | 3 | 22-2-2018 | 13:40:28 | 0:00:00 |
155214 | 4 | 422200253991 | 22-2-2018 | 13:42:47 | 4 | 22-2-2018 | 14:09:05 | 0:04:38 |
155214 | 5 | 422203426171 | 22-2-2018 | 13:47:25 | 5 | 22-2-2018 | 14:09:05 | 0:21:28 |
155214 | 6 | 422203534323 | 22-2-2018 | 14:08:53 | 6 | 22-2-2018 | 14:09:05 | 0:00:12 |
155214 | 7 | 422203700110 | 22-2-2018 | 14:09:57 | 7 | 22-2-2018 | 14:10:58 | 0:01:01 |
155214 | 8 | 422200253991 | 22-2-2018 | 14:10:45 | 8 | 22-2-2018 | 14:10:45 | 0:00:00 |
155215 | 1 | 422203700110 | 0:00:00 | 1 | 23-2-2018 | 0:10:22 | ||
155216 | 1 | 422200253998 | 1:00:00 | 1 | 23-2-2018 | 1:10:22 |
May be this
Table:
LOAD *,
RangeSum([Confirmation date first step], [Confirmation time first step]) as [Confirmation datetime first step],
RangeSum([Confirmation date], [Confirmation time]) as [Confirmation datetime],
AutoNumber(RowNo(), [TO Nr.]) as Rank_New,
If(Len(Trim([Confirmation date first step])) = 0, 1, 0) as NullFlag;
LOAD * INLINE [
TO Nr., TO item, Material, Confirmation date first step, Confirmation time first step, Rank, Confirmation date, Confirmation time
155214, 1, 422203700110, 22-2-2018, 13:19:00, 1, 22-2-2018, 13:19:39
155214, 2, 423509910411, 22-2-2018, 13:20:53, 2, 22-2-2018, 14:09:05
155214, 3, 422200253991, 22-2-2018, 13:40:28, 3, 22-2-2018, 13:40:28
155214, 4, 422200253991, 22-2-2018, 13:42:47, 4, 22-2-2018, 14:09:05
155214, 5, 422203426171, 22-2-2018, 13:47:25, 5, 22-2-2018, 14:09:05
155214, 6, 422203534323, 22-2-2018, 14:08:53, 6, 22-2-2018, 14:09:05
155214, 7, 422203700110, 22-2-2018, 14:09:57, 7, 22-2-2018, 14:10:58
155214, 8, 422200253991, 22-2-2018, 14:10:45, 8, 22-2-2018, 14:10:45
155214, 9, 422200253991, , , 9, 22-2-2018, 14:10:45
];
FinalTable:
LOAD *,
Interval(If(NullFlag = 0,
If([Confirmation datetime] = Previous([Confirmation datetime]), Previous([Confirmation datetime first step]) - [Confirmation datetime first step], [Confirmation datetime] - [Confirmation datetime first step]))) as [Picking Time]
Resident Table
Order By [TO Nr.], [Confirmation datetime first step] desc;
DROP Table Table;
Here you go
Table:
LOAD *,
RangeSum([Confirmation date first step], [Confirmation time first step]) as [Confirmation datetime first step],
RangeSum([Confirmation date], [Confirmation time]) as [Confirmation datetime],
AutoNumber(RowNo(), [TO Nr.]) as Rank_New,
If(Len(Trim([Confirmation date first step])) = 0, 1, 0) as NullFlag;
LOAD * INLINE [
TO Nr., TO item, Material, Confirmation date first step, Confirmation time first step, Rank, Confirmation date, Confirmation time, F9
155214, 1, 422203700110, 22-2-2018, 13:19:00, 1, 22-2-2018, 13:19:39
155214, 2, 423509910411, 22-2-2018, 13:20:53, 2, 22-2-2018, 14:09:05
155214, 3, 422200253991, 22-2-2018, 13:40:28, 3, 22-2-2018, 13:40:28
155214, 4, 422200253991, 22-2-2018, 13:42:47, 4, 22-2-2018, 14:09:05
155214, 5, 422203426171, 22-2-2018, 13:47:25, 5, 22-2-2018, 14:09:05
155214, 6, 422203534323, 22-2-2018, 14:08:53, 6, 22-2-2018, 14:09:05
155214, 7, 422203700110, 22-2-2018, 14:09:57, 7, 22-2-2018, 14:10:58
155214, 8, 422200253991, 22-2-2018, 14:10:45, 8, 22-2-2018, 14:10:45
155215, 1, 422203700110, , 0:00:00, 1, 23-2-2018, 0:10:22,
155216, 1, 422200253998, , 1:00:00, 1, 23-2-2018, 1:10:22
];
FinalTable:
LOAD *,
Interval(If(NullFlag = 0,
If([Confirmation datetime] = Previous([Confirmation datetime]), Previous([Confirmation datetime first step]) - [Confirmation datetime first step], [Confirmation datetime] - [Confirmation datetime first step]))) as [Picking Time]
Resident Table
Order By [TO Nr.], [Confirmation datetime first step] desc;
DROP Table Table;