topic Re: split duration into separate hours in QlikView

split duration into separate hours

mazacini — Mon, 10 Sep 2012 18:18:51 GMT

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?

Re: split duration into separate hours

Tue, 11 Sep 2012 11:09:55 GMT

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

hic — Tue, 11 Sep 2012 15:53:38 GMT

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

Re: split duration into separate hours

mazacini — Tue, 11 Sep 2012 19:00:22 GMT

Hi Vicky

I only have PE.

Could you possibly send me script file?

Joe

Re: split duration into separate hours

mazacini — Tue, 11 Sep 2012 21:00:02 GMT

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

Re: split duration into separate hours

swuehl — Tue, 11 Sep 2012 21:19:08 GMT

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.

Re: split duration into separate hours

Gysbert_Wassenaar — Thu, 13 Sep 2012 11:10:08 GMT

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.

Re: split duration into separate hours

mazacini — Mon, 17 Sep 2012 12:20:14 GMT

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;

Re: split duration into separate hours

mazacini — Mon, 17 Sep 2012 12:21:59 GMT

Thank you to everyone who helped.

Apologies for delay in responding, I got sidetracked!

Here is my final script:

Re: split duration into separate hours

mazacini — Mon, 17 Sep 2012 16:20:08 GMT

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

Re: split duration into separate hours

hic — Mon, 17 Sep 2012 16:51:38 GMT

Not sure it's the most beautiful solution, but it will probably work...

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

While Start+(iterno()-1)/24 <= Finish;

Load

Name,

Start,

If(Finish<Start,Finish+1,Finish) as Finish

FROM (ooxml, embedded labels, table is Sheet1);

HIC

Re: split duration into separate hours

mazacini — Mon, 17 Sep 2012 21:27:15 GMT

Hi Henric

Works perfectly.

Can I ask one more question?

I want to add additional fields

RowNo() as Rec#,

         Date(Date,'dd-MMM-yy') as Date,
     Dept,
     FixHrs as TtlHrs,
     Num(FixRate,'##.00') as Rate,

I inserted these fields after the "Name" field in each of the load statements, but I get "Field Not Found" error message.

Cam you help me on this one?

Thank you

Joe

Re: split duration into separate hours

hic — Tue, 18 Sep 2012 06:50:33 GMT

The second (lower) load is executed first and then the result is piped into the first one. So, the first load works as a filter, or additional transformation of data.

In your case, this means that you need to have the fields you mention is both Load statements, so that they are passed on and are part of the final output. So, you need to repead the field names in both loads or use a * in the upper load:

Load *,

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

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

While Start+(iterno()-1)/24 <= Finish;

Load

Name,

RowNo() as Rec#,

Date(Date,'dd-MMM-yy') as Date,

Dept,

FixHrs as TtlHrs,

Num(FixRate,'##.00') as Rate,

Start,

If(Finish<Start,Finish+1,Finish) as Finish

FROM (ooxml, embedded labels, table is Sheet1);

HIC

Re: split duration into separate hours

mazacini — Thu, 11 Oct 2012 10:52:45 GMT

Hi Henric

Just reviewing this thread, and realised I hadn't acknowledged your post.

It worked great.

Thanks for your help.

Rgds

Joe