topic Re: Load Script in QlikView

Load Script

Thu, 13 Dec 2012 11:58:16 GMT

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

jonathandienst — Thu, 13 Dec 2012 12:09:47 GMT

Something like

LOAD [workflow id],

sum(workinghourscalc) As SumHours

Resident ....

Group by [workflow id];

Regards

Jonathan

Re: Load Script

Peter_Cammaert — Thu, 13 Dec 2012 12:14:59 GMT

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

Thu, 13 Dec 2012 13:06:22 GMT

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

Re: Load Script

Peter_Cammaert — Thu, 13 Dec 2012 13:15:48 GMT

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

Re: Load Script

Thu, 13 Dec 2012 13:20:06 GMT

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

Thu, 13 Dec 2012 13:20:15 GMT

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

jagannalla — Thu, 13 Dec 2012 13:37:22 GMT

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

Thu, 13 Dec 2012 16:04:40 GMT

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

Re: Load Script

Thu, 13 Dec 2012 16:04:46 GMT

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

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

Re: Load Script

jagannalla — Fri, 14 Dec 2012 10:33:12 GMT

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