Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

mazacini
Contributor 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

Re: split duration into separate hours

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

13 Replies
Not applicable

Re: split duration into separate hours

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

Re: split duration into separate hours

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
Contributor III

Re: split duration into separate hours

Hi Vicky

I only have PE.

Could you possibly send me script file?

Ta

Joe

mazacini
Contributor III

Re: split duration into separate hours

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

MVP
MVP

Re: split duration into separate hours

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.

MVP & Luminary
MVP & Luminary

Re: split duration into separate hours

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
Contributor III

Re: split duration into separate hours

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
Contributor III

Re: split duration into separate hours

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
Contributor III

Re: split duration into separate hours

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