Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi
Something like
LOAD [workflow id],
sum(workinghourscalc) As SumHours
Resident ....
Group by [workflow id];
Regards
Jonathan
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
I get an error when i reload the script it says table not found?
Well, the examples were not to Copy&Paste since we haven't seen your actual script. Adapt table and field names to real names and try again.
Otherwise include your script in your next post, then we can have a look. Thanks.
Best,
Peter
Hi i have copied a bit of the script for you to see:
Load
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 .....
Hi i have copied a bit of the script for you to see:
Load
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 .....
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
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 ID Status Start Date End Date Description 1 1/11/12 2/11/12 New Case 1 2/11/12 4/11/12 PreOrder Check 1 5/11/12 7/11/12 Order Approved 1 7/11/12 Order Closed 1 4/11/12 5/11/12 Query
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 ID | Status Start Date | End Date | Description | CURRENT STATUS |
---|---|---|---|---|
1 | 1/11/12 | 2/11/12 | New Case | Order Closed |
1 | 2/11/12 | 4/11/12 | PreOrder Check | Order Closed |
1 | 5/11/12 | 7/11/12 | Order Approved | Order Closed |
1 | 7/11/12 | Order Closed | Order Closed | |
1 | 4/11/12 | 5/11/12 | Query | Order Closed |
Thanks
Laura
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 ID Status Start Date End Date Description 1 1/11/12 2/11/12 New Case 1 2/11/12 4/11/12 PreOrder Check 1 5/11/12 7/11/12 Order Approved 1 7/11/12 Order Closed 1 4/11/12 5/11/12 Query
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 ID | Status Start Date | End Date | Description | CURRENT STATUS |
---|---|---|---|---|
1 | 1/11/12 | 2/11/12 | New Case | Order Closed |
1 | 2/11/12 | 4/11/12 | PreOrder Check | Order Closed |
1 | 5/11/12 | 7/11/12 | Order Approved | Order Closed |
1 | 7/11/12 | Order Closed | Order Closed | |
1 | 4/11/12 | 5/11/12 | Query | Order Closed |
Thanks
Laura