Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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);
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
What error(s) are you getting?
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.
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'
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
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
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
where Date#(Calls.Date, 'DD/MM/YYYY') >= makedate(2013,12,28)
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