Rank and check a field and get sum

[Anonymous]

Need to calculate time based on ranking and confirmation time. For Rank 5 the confirmation time of rank 6 is equal then we need to minus Confirmation time first step. i.e. 14.08.53 - 13.47.35 = 21 else confirmation time - Confirmation time first step

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

[Anonymous]

I am getting below error in date field

[sunny_talwar]

Sorry, what is the error?

[Anonymous]

for function RangeSum([Confirmation date first step], [Confirmation time first step]) as [Confirmation datetime first step],. date field is getting wrong.

[sunny_talwar]

Is your date read a date field?

Why don’t my dates work?

Get the Dates Right

QlikView Date fields

[Anonymous]

Records are getting duplicated if I have new TO Nr

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
155215	1	422199215809	22-2-2018	14:08:53	1	22-2-2018	14:09:05
155215	2	422197575643	22-2-2018	14:09:57	2	22-2-2018	14:10:58
155215	3	422195935477	22-2-2018	14:10:45	3	22-2-2018	14:10:45
155215	4	422194295311	22-2-2018	14:08:53	4	22-2-2018	14:09:05

[Anonymous]

TO Nr gets duplicated with script. Please can you help

[sunny_talwar]

Can you pinpoint where do you see the duplication?

[Anonymous]

Sorry it was my mistake.. not duplicate.. But first record i.e. 4.4:36 - 3.56:49 = 7 because confirmation date is same

• If for a TO item its rank X has the same confirmation date and time as transfer order item X+1 then the picking time = Confirmation date and time of (X+1) first step -  confirmation date of X first step

[sunny_talwar]

Can you attach the file you are looking at? It might be easy to find the error if I have the code in front of me for the data you have used

[Anonymous]

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