Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Chart vs Load Statement Calculation Question

I have a detailed question about whether to keep the calculation as part of a chart or to allow the Load statement of my script to do the heavy lifting.  The current process is I have a script that looks like the following:

WorkOrders:

LOAD Text(AcctNum) as AcctNum,

     ActualCompletionDate,

     Makedate(year(CreatedOnDate),month(CreatedOnDate),day(CreatedOnDate)) as CreateDate,

     Year(CreatedOnDate) as WOYear,

     Month(CreatedOnDate) as WOMonth,

     ActualCompletionDate - CreatedOnDate as AgeofWO,

    WOID,

     StatusDesc,

     PriorityDesc,

     PMScheduledAttached

FROM

WorkOrder.qvd

(qvd);

WorkOrderSummaryCounts:

Load

AcctNum,

WOYear,

WOMonth,

AgeofWO,

StatusDesc

Resident WorkOrders

Group by AcctNum|PurposeID, AcctNumUserName,PMScheduleAttached,AgeofWO, WOYear, WOMonth, PriorityID, CraftID, StatusID, AcctNum;

Drop Table WorkOrders;


From there I create a chart that has the following four calculations on it:

1. count( {<PMScheduleAttached= {"-1*"}, StatusDesc = {"Complete", "Closed*"}, AgeofWO = {"<30"}>} WOID) /count({<PMScheduleAttached={"-1"}, StatusDesc = {"Complete", "Closed*"}>}WOID)

2. sum({<AgeofWO = {"<8"}>}WOCount)/sum(WOCount)

3. sum({<StatusDesc = {"Complete", "Closed Work Orders"}, PriorityDesc = {"Low", "Medium", "High"}, AgeofWO = {"<8"},

PMScheduleAttached= {"0"}>}WOCount)

/

sum({<StatusDesc = {"Complete", "Closed Work Orders"}, PriorityDesc = {"Low", "Medium", "High"}, PMScheduleAttached = {""0""}>}WOCount)"

4. sum({<StatusDesc = {"New Request","Work in Progress"},PMScheduleAttached = {"0"}, AgeofWO = {"<2"}>}WOCount)/

sum({<StatusDesc = {"New Request","Work in Progress"},PMScheduleAttached = {"0"}>}WOCount)"

This all works but the size of my file is getting close to 1gb because of the AgeofWO and WOCount.  Is there any way to make the file smaller? 

David

1 Solution

Accepted Solutions
Clever_Anjos
Employee
Employee

I didn´t find any sum,count, into load below, I think you should have some agregation here, right?

WorkOrderSummaryCounts:

Load

AcctNum,

WOYear,

WOMonth,

AgeofWO,

StatusDesc

Resident WorkOrders

Group by AcctNum|PurposeID, AcctNumUserName,PMScheduleAttached,AgeofWO, WOYear, WOMonth, PriorityID, CraftID, StatusID, AcctNum;

View solution in original post

5 Replies
Anonymous
Not applicable
Author

David

For your various date fields, as in these entries from your load script, are the dates true dates of actually being held a date/time fields ?

     ActualCompletionDate,

     Makedate(year(CreatedOnDate),month(CreatedOnDate),day(CreatedOnDate)) as CreateDate,

     Year(CreatedOnDate) as WOYear,

     Month(CreatedOnDate) as WOMonth,

     ActualCompletionDate - CreatedOnDate as AgeofWO,

The reason I asked that as if they being held a date/time fields then they will have a lot more unique values than if they are pure dates.

To convert them to pure dates you need to knock off the decimals, to leave just the integer date, by doing something like:

date ( floor ( [ActualCompletionDate] ) )     as     ActualCompletionDate ,

Best Regards,     Bill

Not applicable
Author

Hi Bill,

They are stored as numbers that represent dates.  That is the reason I convert them.  Is this what you are asking?

David

Anonymous
Not applicable
Author

David

Are they integers, or do they have decimals as well ?

Best Regards,     Bill

Gysbert_Wassenaar

I think what you posted is not your actual script. The WorkOrderSummaryCounts loads a field AcctNum|PurposeID that doesn't seem to exist in WorkOrders. Also you have a group by clause, but no aggregation function.

If WorkOrders and WorkOrderSummaryCounts have a lot of field names in common you will get a synthetic key. That could be one reason for the increase in file size. You could consider concatenating the summary table to the WorkOrders table. Something like:

WorkOrders:

Load *, 'detail' as RecordType from ....

concatenate(WorkOrders)

Load X,Y,Z, count(Q) as CountQ, 'summary' as RecordType

resident WorkOrders

group by X,Y,Z;


talk is cheap, supply exceeds demand
Clever_Anjos
Employee
Employee

I didn´t find any sum,count, into load below, I think you should have some agregation here, right?

WorkOrderSummaryCounts:

Load

AcctNum,

WOYear,

WOMonth,

AgeofWO,

StatusDesc

Resident WorkOrders

Group by AcctNum|PurposeID, AcctNumUserName,PMScheduleAttached,AgeofWO, WOYear, WOMonth, PriorityID, CraftID, StatusID, AcctNum;