Rank and check a field and get sum

[Anonymous]

If any of the date and time field is null then picking time shud be blank...

[sunny_talwar]

It would be helpful if you can provide the sample data as well.... so that we can test it out

[Anonymous]

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

[sunny_talwar · Accepted solution]

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;

[sunny_talwar]

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;