topic AGGR Syntax in QlikView

AGGR Syntax

cbaqir — Fri, 16 Jun 2017 21:16:41 GMT

This should be a simple answer (I hope).

If you select PP_NAME = Vasoactive Agents (Pressors), the Usage chart shows 1226 but the table shows 1226 for each Domain with a total of 2452.

The counts per domain should be CAREB = 765 and ECISA = 461 for a total of 1226

What's wrong with my syntax for Usage Count?

sum(aggr(sum({$}CNT_USAGE),DOMAIN,GOVERNING_CCG,PP_NAME,LAST_REVIEW_DATE))

Re: AGGR Syntax

vishsaggi — Fri, 16 Jun 2017 21:25:49 GMT

How are you getting this 765 for CAREB? I tried in your document, it shows 1226? Can you tell me how i can see 765?

Re: AGGR Syntax

cbaqir — Fri, 16 Jun 2017 21:27:40 GMT

I did a pivot table on the raw data. Maybe there's an issue with my join?

Re: AGGR Syntax

Anonymous — Fri, 16 Jun 2017 21:40:48 GMT

How do you knwow CAREB should be 765 and ECISA should be 461? Looking at your tables in your data model I don't see what would tell me that.

The join between your EKG_PP and your Usage tables don't take into account the Domain so your total is doubling.

Re: AGGR Syntax

cbaqir — Fri, 16 Jun 2017 21:50:53 GMT

Re: AGGR Syntax

Anonymous — Fri, 16 Jun 2017 21:58:08 GMT

For a pivot table in Excel, your data source would be 1 big table. But that's not what your data model looks like in Qlikview. You have the usage and Domain in 2 different tables.

Looking at your script you're taking the 1 excel table and making multiple Qlikview tables but without all the appropriate keys.

In your script, you should create a composite key in your tables of VERSION_PW_CAT_ID & '|' & DOMAIN as %Key

(or better yet, autonumber(VERSION_PW_CAT_ID & '|' & DOMAIN) as %Key

or something similar.

And remove VERSION_PW_CAT_ID from your usage table.

Re: AGGR Syntax

Anonymous — Wed, 12 Jul 2017 20:19:31 GMT

Did you figure this out?

If so, please close the thread by marking a reply as correct even if it's your own.

Qlik Community Tip: Marking Replies as Correct or Helpful