13 Replies Latest reply: Oct 11, 2012 6:52 AM by Joe Kirwan RSS

    split duration into separate hours

    Joe Kirwan

      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?

        • 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
              Henric Cronström

              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
                  Joe Kirwan

                  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

                  • Re: split duration into separate hours
                    Joe Kirwan

                    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

                      • Re: split duration into separate hours
                        Henric Cronström

                        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 [c:\times.xlsx] (ooxml, embedded labels, table is Sheet1);

                         

                        HIC

                          • Re: split duration into separate hours
                            Joe Kirwan

                            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
                                Henric Cronström

                                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 [c:\times.xlsx] (ooxml, embedded labels, table is Sheet1);

                                 

                                HIC

                        • Re: split duration into separate hours
                          Joe Kirwan

                          Hi Vicky

                           

                          I only have PE.

                           

                          Could you possibly send me script file?

                           

                          Ta

                           

                          Joe

                        • Re: split duration into separate hours
                          Joe Kirwan

                          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
                          [c:\times.xlsx]
                          (
                          ooxml, embedded labels, table is Sheet1)
                          While Start+(iterno()-1)/24 <= Finish;

                          • Re: split duration into separate hours
                            Joe Kirwan

                            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
                            [c:\times.xlsx]
                            (
                            ooxml, embedded labels, table is Sheet1)
                            While Start+(iterno()-1)/24 <= Finish;