Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

10 Replies
MVP
MVP

Re: Load Script

Hi

Something like

LOAD [workflow id],

     sum(workinghourscalc) As SumHours

Resident ....

Group by [workflow id];

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

Re: Load Script

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

Not applicable

Re: Load Script

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

Re: Load Script

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

Not applicable

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

Not applicable

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

jagannalla
Valued Contributor III

Re: Load Script

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

Not applicable

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

Not applicable

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