Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mazacini
Creator III
Creator III

split duration into separate hours

Hi

I ha

ve a file showing start and finish times like this

NameStartFinish
John11:0013:10
Frank10:1512:15

I want to split it into separate hours like this:

NameStartFinish
John11:0012:00
John12:0013:00
John13:0013:10
Frank10:1511:00
Frank11:0012:00
Frank12:0012:15

Can anyone help me?

1 Solution

Accepted Solutions
hic
Former Employee
Former Employee

There is more than one way to skin a cat...

Load

          Name,

          Time(RangeMax(Start, Floor(Start,1/24)+(iterno()-1)/24),'hh:mm') as Start,

          Time(RangeMin(Finish, Floor(Start,1/24)+iterno()/24),'hh:mm') as Finish

inline

[Name          Start          Finish

John          11:00          13:10

Frank          10:15          12:15]

(delimiter is '\t') While Start+(iterno()-1)/24 <= Finish;

HIC

View solution in original post

13 Replies
Not applicable

Hi,

Maybe the script is a little bit inconvenient, but I could not persuade QV to do it other way.

I hope, you can add it on your real data.

Regards Vicky

hic
Former Employee
Former Employee

There is more than one way to skin a cat...

Load

          Name,

          Time(RangeMax(Start, Floor(Start,1/24)+(iterno()-1)/24),'hh:mm') as Start,

          Time(RangeMin(Finish, Floor(Start,1/24)+iterno()/24),'hh:mm') as Finish

inline

[Name          Start          Finish

John          11:00          13:10

Frank          10:15          12:15]

(delimiter is '\t') While Start+(iterno()-1)/24 <= Finish;

HIC

mazacini
Creator III
Creator III
Author

Hi Vicky

I only have PE.

Could you possibly send me script file?

Ta

Joe

mazacini
Creator III
Creator III
Author

Hi Henric

EDIT: I replaced the inline load below with a table load from excel, and it worked perfectly! See load script in post Sep 17th

Thank you for your response.

I'm getting the following script error:

Duration Split.PNG

Can you helo?

Thanks

Joe

swuehl
MVP
MVP

Check that your inline table is still delimited by tabs. They got probably lost.

Try using a different delimiter (delimiter is " ") or comma separated values.

Gysbert_Wassenaar

When you copy and paste code from the forums you may need to edit it a bit to get rid of extra lines and other messed up white space. In this case make sure the values in the inline (the stuff between the [ ] brackets) are separated by tabs and not a number of spaces.


talk is cheap, supply exceeds demand
mazacini
Creator III
Creator III
Author

Hi

Thanks to all who helped. Apologies for delay, I got sidetracked.

Here is my final script, and it worked perfectly:

Load

    
Name,

   
Time(RangeMax(Start, Floor(Start,1/24)+(iterno()-1)/24),'hh:mm') as Start,

   
Time(RangeMin(Finish, Floor(Start,1/24)+iterno()/24),'hh:mm') as Finish
FROM

(
ooxml, embedded labels, table is Sheet1)
While Start+(iterno()-1)/24 <= Finish;

mazacini
Creator III
Creator III
Author

Thank you to everyone who helped.

Apologies for delay in responding, I got sidetracked!

Here is my final script:

Load

    
Name,

   
Time(RangeMax(Start, Floor(Start,1/24)+(iterno()-1)/24),'hh:mm') as Start,

   
Time(RangeMin(Finish, Floor(Start,1/24)+iterno()/24),'hh:mm') as Finish
FROM

(
ooxml, embedded labels, table is Sheet1)
While Start+(iterno()-1)/24 <= Finish;

mazacini
Creator III
Creator III
Author

Hi Henric

I'm not sure if you are still following this post.

Would you be able to tell me how I would get around the problem where finish time runs past midnight?

NameStartFinish
John18:0001:25
Frank21:0003:10

I'd appreciate it if you could help!

Joe