Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

HELP! agreagate data based on date and multiple metrics

Hello All!

I have a table i am bringing in from Excel, it has call center CALL data, and SECONDS that is broken up by MONTH, DATE, LANGUAGE, [CALL TYPE], and STRATEGY, taken by LEVEL 1, and rolled up to LEVEL 2, and LEVEL 3. These are all my metrics.

What i am attempting to do is sum up all the SECONDS, and CALLS up to the STRATEGY metric to minimize space in my table.

I am looking for solutions but I am having difficulty finding a SUM or AGGR for multiple criteria DATE, LANGUAGE, [CALL TYPE], LEVEL 1, LEVEL 2, LEVEL 3, STRATEGY... I also would like to do this from the script as i am loading the information from the spreadsheet.

I appreciate the help of the QlikView community

6 Replies
Employee
Employee

Re: HELP! agreagate data based on date and multiple metrics

If you want to to it at script level, don´t use AGGR

tmp:

LOAD *

from yourexcelfile.xls;

LOAD

DATE, LANGUAGE, [CALL TYPE], LEVEL 1, LEVEL 2, LEVEL 3, STRATEGY,

SUM(SECONDS) AS TOTAL_SECONDS

SUM(CALLS)  AS TOTAL_CALLS

RESIDENT tmp

GROUP BY

DATE, LANGUAGE, [CALL TYPE], LEVEL 1, LEVEL 2, LEVEL 3, STRATEGY;


DROP TABLE tmp:

Not applicable

Re: HELP! agreagate data based on date and multiple metrics

Thank you Clever,

I'm am a beginner and frustration is kicking in. what is the syntax in this?

having issues with way i am writing the script

Employee
Employee

Re: HELP! agreagate data based on date and multiple metrics

Would you mind sharing a sample of your data?

I´ll write the script to you

Not applicable

Re: HELP! agreagate data based on date and multiple metrics

I guess my issue is more as to where does the SUM need to be, and where does the GROUP BY need to be, same as the coma (",") and semi (";") placement need to be.

Also, i have never worked with adding a RESIDENT tmp so does that belong on the top bottom, etc..... i provided an image of all the info of how i pull below..

Not applicable

Re: HELP! agreagate data based on date and multiple metrics

Thank you for your offer Clever, i have to override many names and information to give you a sample, does this help you help me? Thank you very much for your offer. It is much appreciated!

Employee
Employee

Re: HELP! agreagate data based on date and multiple metrics

Qlikview does no support group by direct from a excel file

Please

1) Give a name to your table putting a name + ":" before LOAD

tmp:

LOAD

  DATE

2) Now we can aggregate

LOAD

   DATE,

   Language,

   [CallType]

  SUM(SECONDS) AS TOTAL_SECONDS,

RESIDENT tmp

GROUP BY

   DATE,

   Language,

   [CallType];


DROP TABLE tmp


Community Browser