Where clause to minimise the load

[bobbydave]

I am trying to minimise my load by taking from 28/12/2013 data onwards and am wondering if anyone can help with my Where clause

Keep getting errors.

Any ideas?

A_Call:
LOAD Calls.CountryCode & Date(Date#(Calls.Date, 'DD/MM/YYYY'), 'YYYYMM') &  SKILL_NUMBER & Calls.isNonClaim as %Call,
Calls.CountryCode,
Date(Date#(Calls.Date, 'DD/MM/YYYY'), 'YYYYMM') as Calls.Date,
SKILL_NUMBER,
ApplyMap('MapCallSkillNoToName', SKILL_NUMBER) as SKILL_NAME,
Calls.isNonClaim,
ASA,
sum(CALLS_RECEIVED) as CALLS_RECEIVED,
sum(CALLS_ANSWERED) as CALLS_ANSWERED,
sum(CALLS_ABANDONED) as CALLS_ABANDONED,
sum(CALLS_TRANSFERRED) as CALLS_TRANSFERRED,
//avg(ASA) as ASA,
   sum(ASA) as SumASA,
max(MAXIMUM_ANSWER_DELAY) as MAXIMUM_ANSWER_DELAY,
sum(CUST_ABANDONED) as CUST_ABANDONED,
sum(CALLS_ANSWERED_WITHIN_SLA) as CALLS_ANSWERED_WITHIN_SLA,
sum(CUST_OFFERED) as CUST_OFFERED//,


FROM
[..\QVDs\T_Call.qvd]
(qvd) where Date(Date#(Calls.Date, 'DD/MM/YYYY'), 'DD/MM/YYYY') >= '28/12/2013';
//group by Calls.CountryCode, Calls.Date, SKILL_NUMBER, Calls.isNonClaim, ASA;


Store A_Call into ..\QVDs\A_Call_CommSummOp.qvd(qvd);

[marcus_sommer]

You used aggregations within the load therefore you need the uncomment group by statement, too.

Often you get a better load-performance if you split both parts and filters in a first load your data with the where-clause and in a second load you aggregates your data.

- Marcus

[Peter_Cammaert]

What error(s) are you getting?

[malini_qlikview]

Hi,

can you please provide some additional information like, what is the calls.date field format in the qvd and what is the error message you get. If possible try attaching a sample file.

[bobbydave]

Invalid expression
A_Call:
LOAD Calls.CountryCode & Date(Date#(Calls.Date, 'DD/MM/YYYY'), 'YYYYMM') &  SKILL_NUMBER & Calls.isNonClaim as %Call,
  Calls.CountryCode,
 
  Date(Date#(Calls.Date, 'DD/MM/YYYY'), 'YYYYMM') as Calls.Date,
  SKILL_NUMBER,
  ApplyMap('MapCallSkillNoToName', SKILL_NUMBER) as SKILL_NAME,
  Calls.isNonClaim,
  ASA,
     sum(CALLS_RECEIVED) as CALLS_RECEIVED,
     sum(CALLS_ANSWERED) as CALLS_ANSWERED,
     sum(CALLS_ABANDONED) as CALLS_ABANDONED,
     sum(CALLS_TRANSFERRED) as CALLS_TRANSFERRED,
    
     sum(ASA) as SumASA,
     max(MAXIMUM_ANSWER_DELAY) as MAXIMUM_ANSWER_DELAY,
     sum(CUST_ABANDONED) as CUST_ABANDONED,
     sum(CALLS_ANSWERED_WITHIN_SLA) as CALLS_ANSWERED_WITHIN_SLA,
     sum(CUST_OFFERED) as CUST_OFFERED

FROM
[..\QVDs\T_Call.qvd]
(qvd) where Date(Date#(Calls.Date, 'DD/MM/YYYY'), 'DD/MM/YYYY') >= '28/12/2013'

[Peter_Cammaert]

Marcus already pointed out what causes this message. You cannot aggregate (e.g. using Sum()) without a GROUP BY clause. Uncomment the GROUP BY line and everything should be fine.

Peter

[bobbydave]

Original date format is DD/MM/YYYY

In the code, I've converted it to YYYYMM so once compiled it comes out as YYYYMM

but when I originally load it, it will load as DD/MM/YYYY

[bobbydave]

Hi Marcus,

I put back in the group by clause and the loader compiled without errors.

However, this didn't make a change. The data is still showing dates from 2010. I just want it to show from 28/12/2013

Date(Date#(Calls.Date, 'DD/MM/YYYY'), 'YYYYMM') as Calls.Date

My original date is coming as DD/MM/YYYY so I'm assuming my where clause needs to take that date into consideration and not that I've transformed it to YYYYMM in the loader as above.

where Date(Date#(Calls.Date, 'DD/MM/YYYY'), 'DD/MM/YYYY') >= '28/12/2013'

I've tried

where Date(Calls.Date, 'DD/MM/YYYY' >= '28/12/2013'

and nothing works to minimise the load

[simenkg · Accepted solution]

where Date#(Calls.Date, 'DD/MM/YYYY') >= makedate(2013,12,28)

[marcus_sommer]

An statement like yours - '28/12/2013' - worked only if this is the default date-format within the qvw. If not it won't be interpretet as date it would be anything else and often be only a string. This is main reason why you shouldn't never use date-formats within any matchings or calculation else pure numeric values which you could get with:

num(date) or num(date#(date, Format))

This avoids many problems with this format-stuff especially if you have multiple date-fields and it will be mostly perform better because there is no parsing-overhead.

In the long-term you shoud consider to change your date-logics but for now should the suggestion from bwisenoSimenKG work, too.

- Marcus