topic Where clause in QlikView

Where clause

bobbydave — Fri, 20 Nov 2020 18:02:30 GMT

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)
group by Calls.CountryCode, Calls.Date, SKILL_NUMBER, Calls.isNonClaim, ASA

where Year( Calls.Date ) > '2014';

I was using

If(Year(Date(Date#(Calls.Date, 'DD/MM/YYYY'), 'YYYYMM')) >= 2014, Date(Date#(Calls.Date, 'DD/MM/YYYY'), 'YYYYMM')) as Calls.Date,

within the app but then, Calls.Date will still have pre-2014 data and I don't want this.

I am guessing the Where clause can ONLY bring in 2014 and post 2014 data.

Any ideas on how to get this Where clause to work? What I currently have doesn't work

Re: Where clause

alexandros17 — Tue, 22 Sep 2015 13:43:48 GMT

write Where clause before Group by

Re: Where clause

bobbydave — Tue, 22 Sep 2015 13:53:49 GMT

Doesn't like my Where clause no matter what I write.

I've tried

Where Calls.Date >= '28/12/2013'

Where Years(Date) >= '2014'

and doesn't like whatever I put in.

Re: Where clause

sasiparupudi1 — Tue, 22 Sep 2015 13:53:50 GMT

Try

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 Year( Calls.Date ) > '2014'

group by (Calls.CountryCode & Date(Date#(Calls.Date, 'DD/MM/YYYY'), 'YYYYMM') & SKILL_NUMBER & Calls.isNonClaim),

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

ApplyMap('MapCallSkillNoToName', SKILL_NUMBER), Calls.isNonClaim, ASA

Re: Where clause

cjohnson — Tue, 22 Sep 2015 20:07:23 GMT

Have you tried the following?

Where Year(Calls.Date) >= 2014

Re: Where clause

maxgro — Tue, 22 Sep 2015 20:10:29 GMT

..............

FROM [..\QVDs\T_Call.qvd] (qvd)

where Year( Calls.Date ) > 2014
group by Calls.CountryCode, Calls.Date, SKILL_NUMBER, Calls.isNonClaim, ASA;