QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Rules, plus terms and conditions, can be found here.
Contributor II

Help please - Using peek - Calculate time difference from above record

Hello Everyone,

I'm trying to create a field with time difference between runners from the above  -

Group by Tournament, Sorted by Completion time.

First perosn who has completed will get 'First COmplete' Test.

Below is my data set and highlighted is my expected output.

 Tournament Runner Completed Diff in Hours compared to previous one(Excepted field for output) XYZ K 2/1/2018 10:31 First complete XYZ L 2/1/2018 13:01 2.4975 hrs XYZ M 2/1/2018 13:01 0 hrs ABC A 1/15/2018 13:02 First complete ABC D 1/15/2018 13:02 0 hrs ABC B 1/25/2018 23:02 250 hrs

Any help is highly appreciated!!!

Message was edited by: Mark Graham

1 Solution

Accepted Solutions
MVP

Re: Calculate time difference in a data set

Not sure, but something like this

Table1:

Tournament, Runner, Completed

XYZ, A, 1/5/2018 13:02

XYZ, B, 1/5/2018 13:02

XYZ, C, 1/5/2018 13:02

XYZ, D, 1/5/2018 13:02

XYZ, E, 1/7/2018 6:16

XYZ, F, 1/13/2018 3:30

XYZ, G, 1/16/2018 9:31

XYZ, H, 1/16/2018 14:46

XYZ, I, 1/20/2018 6:47

XYZ, J, 1/22/2018 13:31

XYZ, K, 2/1/2018 10:31

XYZ, L, 2/1/2018 13:01

XYZ, M, 2/1/2018 13:01

ABC, A, 1/15/2018 13:02

ABC, B, 1/25/2018 23:02

ABC, C, 1/10/2018 13:02

ABC, D, 1/15/2018 13:02

ABC, E, 1/7/2018 6:16

];

FinalTable:

If(Tournament = Previous(Tournament), Completed - Previous(Completed), 0) * 24 as Difference

Resident Table1

Order By Tournament, Completed;

DROP Table Table1;

6 Replies
Contributor II

MVP

Re: Calculate time difference in a data set

Something like this?

Contributor II

Re: Calculate time difference in a data set

Sunny -

I wanted to do that in script.

Contributor II

Re: Calculate time difference in a data set

Sunny -

Please see updated application and output.

Thanks!

MVP

Re: Calculate time difference in a data set

Not sure, but something like this

Table1:

Tournament, Runner, Completed

XYZ, A, 1/5/2018 13:02

XYZ, B, 1/5/2018 13:02

XYZ, C, 1/5/2018 13:02

XYZ, D, 1/5/2018 13:02

XYZ, E, 1/7/2018 6:16

XYZ, F, 1/13/2018 3:30

XYZ, G, 1/16/2018 9:31

XYZ, H, 1/16/2018 14:46

XYZ, I, 1/20/2018 6:47

XYZ, J, 1/22/2018 13:31

XYZ, K, 2/1/2018 10:31

XYZ, L, 2/1/2018 13:01

XYZ, M, 2/1/2018 13:01

ABC, A, 1/15/2018 13:02

ABC, B, 1/25/2018 23:02

ABC, C, 1/10/2018 13:02

ABC, D, 1/15/2018 13:02

ABC, E, 1/7/2018 6:16

];

FinalTable:

If(Tournament = Previous(Tournament), Completed - Previous(Completed), 0) * 24 as Difference

Resident Table1

Order By Tournament, Completed;

DROP Table Table1;

Contributor II

Re: Calculate time difference in a data set

Thank you very much !!