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

HELP! agreagate data based on date and multiple metrics

Thu, 14 May 2015 17:02:47 GMT

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

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

Clever_Anjos — Thu, 14 May 2015 17:49:24 GMT

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:

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

Thu, 14 May 2015 19:34:05 GMT

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

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

Clever_Anjos — Thu, 14 May 2015 19:40:22 GMT

Would you mind sharing a sample of your data?

I´ll write the script to you

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

Thu, 14 May 2015 20:03:12 GMT

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

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

Thu, 14 May 2015 20:08:45 GMT

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!

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

Clever_Anjos — Thu, 14 May 2015 21:03:04 GMT

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