10 Replies Latest reply: Dec 14, 2012 5:33 AM by Jagan Nalla RSS

    Load Script

      Hi,

       

      I want to do a calculation in the load script which says sum(workinghourscalc) for each individual workflow id.

       

      I have calculatd the workinghourscalc in my load script by taking each individual status in 1 workflow and calculating the working hours calculation  for the time it has been in each status.

       

      i now want to sum(workinghourscalc) for each individual workflow id in the load script.

       

      How do i do this?

       

      Thanks

       

      Laura

        • Re: Load Script
          Jonathan Dienst

          Hi

           

          Something like

           

          LOAD [workflow id],

               sum(workinghourscalc) As SumHours

          Resident ....

          Group by [workflow id];

           

          Regards

          Jonathan

          • Re: Load Script
            Peter Cammaert

            You could us a GROUP BY clause. In the following example, a new "Totals" table is created based on your original calculated table:

             

            TotalWorkingHours:

            LOAD workflowID,

                 sum(workinghourscalc) as TotalWorkingHours

            RESIDENT OriginalWorkinghoursTable

            GROUP BY workflowID;

             

            then you can JOIN these totals to each and every record of your original table, or just leave them as an associated table on key workflowID.

             

            Best,

             

            Peter

              • Re: Load Script

                Hi i have copied a bit of the script for you to see:

                 

                Load

                Workflow_ID,
                sum(Status_WorkingHours_Cal) as TotalWorkingHoursCal
                Resident Orders
                Group By Workflow_ID;

                 

                Connect to ABC

                 

                Orders:

                Load ordwf_wip_id as Workflow_ID,

                Interval(
                rangesum(
                NetWorkDays(sts_start_date+1,sts_end_date-1,$(vHol)* MakeTime(8)

                ,if(NetWorkDays(sts_end_date,sts_end_date,$(vHol) ,Rangemin(rangemax(frac(sts_end_date),maketime(9)),maketime(17))-Rangemax(rangemin(frac(sts_end_date),maketime(9)),maketime(9)),0)

                ,if(NetWorkDays(sts_start_date,sts_start_date,$(vHol), Rangemin(rangemax(frac(sts_start_date),maketime(17)),maketime(17))-Rangemax(rangemin(frac(sts_start_date),maketime(17)),maketime(9)),0)

                ,if(NetWorkDays(sts_start_date,sts_start_date,$(vHol) and floor(sts_start_date)=floor(sts_end_date),-MakeTime(8)))
                as Status_WorkingHours_Cal

                SQLSelect ordwf_wip_det.ordwf_wip_id,
                ordwf_wip_det.cust_name,

                etc .....

                 

                 

                • Re: Load Script

                  Hi i have copied a bit of the script for you to see:

                   

                  Load

                  Workflow_ID,
                  sum(Status_WorkingHours_Cal) as TotalWorkingHoursCal
                  Resident Orders
                  Group By Workflow_ID;

                   

                  Connect to ABC

                   

                  Orders:

                  Load ordwf_wip_id as Workflow_ID,

                  Interval(
                  rangesum(
                  NetWorkDays(sts_start_date+1,sts_end_date-1,$(vHol)* MakeTime(8)

                  ,if(NetWorkDays(sts_end_date,sts_end_date,$(vHol) ,Rangemin(rangemax(frac(sts_end_date),maketime(9)),maketime(17))-Rangemax(rangemin(frac(sts_end_date),maketime(9)),maketime(9)),0)

                  ,if(NetWorkDays(sts_start_date,sts_start_date,$(vHol), Rangemin(rangemax(frac(sts_start_date),maketime(17)),maketime(17))-Rangemax(rangemin(frac(sts_start_date),maketime(17)),maketime(9)),0)

                  ,if(NetWorkDays(sts_start_date,sts_start_date,$(vHol) and floor(sts_start_date)=floor(sts_end_date),-MakeTime(8)))
                  as Status_WorkingHours_Cal

                  SQLSelect ordwf_wip_det.ordwf_wip_id,
                  ordwf_wip_det.cust_name,

                  etc .....

                   

                   

                    • Re: Load Script
                      Jagan Nalla

                      Change your code to

                       

                      Connect to ABC

                       

                       

                      Orders:

                       

                      Load ordwf_wip_id as Workflow_ID,

                      Interval(
                      rangesum(
                      NetWorkDays(sts_start_date+1,sts_end_date-1,$(vHol)* MakeTime(8)

                      ,if(NetWorkDays(sts_end_date,sts_end_date,$(vHol) ,Rangemin(rangemax(frac(sts_end_date),maketime(9)),maketime(17))-Rangemax(rangemin(frac(sts_end_date),maketime(9)),maketime(9)),0)

                      ,if(NetWorkDays(sts_start_date,sts_start_date,$(vHol),Rangemin(rangemax(frac(sts_start_date),maketime(17)),maketime(17))-Rangemax(rangemin(frac(sts_start_date),maketime(17)),maketime(9)),0)

                      ,if(NetWorkDays(sts_start_date,sts_start_date,$(vHol) andfloor(sts_start_date)=floor(sts_end_date),-MakeTime(8)))
                      as Status_WorkingHours_Cal

                      SQLSelect ordwf_wip_det.ordwf_wip_id,
                      ordwf_wip_det.cust_name,..

                      from

                      Orders

                       

                      Res_Orders:

                      Load

                      Workflow_ID,
                      sum(Status_WorkingHours_Cal) as TotalWorkingHoursCal
                      Resident Orders
                      Group By Workflow_ID;


                       

                       

                       

                      Cheers!!

                      Jagan Nalla

                        • Re: Load Script

                          Thanks that worked!!

                           

                           

                          I also want to get current status and add it too each line in the workflow.

                           

                          I thought the same approach would work but it just says script load failed.

                           

                          CurrentStatus:

                          Load Workflow_ID,
                          Status_Description as CurrentStatus
                          Resident Orders
                          Where Status_StartDate=max(Status_StartDate)
                          Group By Workflow_ID;

                           

                          the idea i had was to get the current status for each workflow ID then it needs to look at the maximum status date date for each workflow and get that status.

                           

                           

                          For Example

                           

                          Workflow IDStatus Start DateEnd DateDescription
                          11/11/122/11/12New Case
                          12/11/124/11/12PreOrder Check
                          15/11/127/11/12Order Approved
                          17/11/12Order Closed
                          14/11/12 5/11/12Query

                           

                          The logic would be to look at the max status start date which is the 7/11/12 in this case and return order closed. So i would have a new field called current status. So i could make the following table:

                           

                           

                           

                          Workflow IDStatus Start DateEnd DateDescriptionCURRENT STATUS
                          11/11/122/11/12New CaseOrder Closed
                          12/11/124/11/12PreOrder CheckOrder Closed
                          15/11/127/11/12Order ApprovedOrder Closed
                          17/11/12Order ClosedOrder Closed
                          14/11/12 5/11/12QueryOrder Closed

                           

                          Thanks

                           

                          Laura

                          • Re: Load Script

                            Thanks that worked!!

                             

                             

                            I also want to get current status and add it too each line in the workflow.

                             

                            I thought the same approach would work but it just says script load failed.

                             

                            CurrentStatus:

                            Load Workflow_ID,
                            Status_Description as CurrentStatus
                            Resident Orders
                            Where Status_StartDate=max(Status_StartDate)
                            Group By Workflow_ID;

                             

                            the idea i had was to get the current status for each workflow ID then it needs to look at the maximum status date date for each workflow and get that status.

                             

                             

                            For Example

                             

                            Workflow IDStatus Start DateEnd DateDescription
                            11/11/122/11/12New Case
                            12/11/124/11/12PreOrder Check
                            15/11/127/11/12Order Approved
                            17/11/12Order Closed
                            14/11/12 5/11/12Query

                             

                            The logic would be to look at the max status start date which is the 7/11/12 in this case and return order closed. So i would have a new field called current status. So i could make the following table:

                             

                             

                             

                            Workflow IDStatus Start DateEnd DateDescriptionCURRENT STATUS
                            11/11/122/11/12New CaseOrder Closed
                            12/11/124/11/12PreOrder CheckOrder Closed
                            15/11/127/11/12Order ApprovedOrder Closed
                            17/11/12Order ClosedOrder Closed
                            14/11/12 5/11/12QueryOrder Closed

                             

                            Thanks

                             

                            Laura

                              • Re: Load Script
                                Jagan Nalla

                                Hello,

                                 

                                I think this code will help you. Kindly see attached file for your reference.

                                 

                                T1:

                                LOAD WorkflowID,Date#(StatusStartDate,'DD/MM/YY') as StatusStartDate,Date#(EndDate,'DD/MM/YY') as EndDate,Description;

                                LOAD * Inline [

                                WorkflowID,StatusStartDate,EndDate,Description

                                1,1/11/12,2/11/12,NewCase

                                1,2/11/12,4/11/12,PreOrderCheck

                                1,5/11/12,7/11/12,OrderApproved

                                1,7/11/12,,OrderClosed

                                1,4/11/12,5/11/12,Query

                                2,5/11/12,,Query

                                2,1/11/12,2/11/12,NewCase

                                2,4/11/12,5/11/12,OrderApproved

                                ];

                                 

                                 

                                MAP1:

                                Mapping

                                LOAD Num(WorkflowID)&'-'&Num(StatusStartDate) as Key,Description Resident T1;

                                 

                                 

                                T2:

                                LOAD WorkflowID,ApplyMap('MAP1',Num(WorkflowID)&'-'&Num(Date(Max(StatusStartDate),'DD/MM/YY'))) as CurrentStatus Resident T1 Group By WorkflowID;

                                 

                                Cheers!!

                                Jagan Nalla