Load Script

[#1]

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

[jonathandienst]

Hi

Something like

LOAD [workflow id],

     sum(workinghourscalc) As SumHours

Resident ....

Group by [workflow id];

Regards

Jonathan

[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

[#4]

I get an error when i reload the script it says table not found?

[Peter_Cammaert]

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

[#6]

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 .....

[#7]

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 .....

[jagannalla]

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

[#9]

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

[#10]

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