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?

[hic]

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

[mazacini]

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

[hic]

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

[mazacini]

Hi Henric

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

It worked great.

Thanks for your help.

Rgds

Joe