Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
bobbydave
Creator III
Creator III

Where clause to minimise the load

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);

1 Solution

Accepted Solutions
simenkg
Partner
Partner

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


View solution in original post

9 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

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
Partner
Partner

What error(s) are you getting?

malini_qlikview
Creator II
Creator II

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
Creator III
Creator III
Author

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
Partner
Partner

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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
Partner
Partner

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


View solution in original post

marcus_sommer
MVP & Luminary
MVP & Luminary

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