split duration into separate hours

[mazacini]

Hi

I ha

ve a file showing start and finish times like this

Name	Start	Finish
John	11:00	13:10
Frank	10:15	12:15

I want to split it into separate hours like this:

Name	Start	Finish
John	11:00	12:00
John	12:00	13:00
John	13:00	13:10
Frank	10:15	11:00
Frank	11:00	12:00
Frank	12:00	12:15

Can anyone help me?

[#2]

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 · Accepted solution]

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]

Hi Vicky

I only have PE.

Could you possibly send me script file?

Ta

Joe

[mazacini]

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:

Can you helo?

Thanks

Joe

[swuehl]

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.

[mazacini]

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]

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]

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?

Name	Start	Finish
John	18:00	01:25
Frank	21:00	03:10

I'd appreciate it if you could help!

Joe