Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculated dimension - Need help to understand one line script

This is the line which creates a calculated dimension for a straight table -

Aggr(Count(DISTINCT [From - To State Code]), [Carrier Name])

Carrier Name holds the names of commercial airline companies. From-To state refers to states in the USA.

How does qlikview process this line of script ? Can someone show this graphically ?

Thanks.

4 Replies
maxgro
MVP
MVP

graphically ?


make a chart with

dimension     Carrier Name

expression     Count(DISTINCT [From - To State Code])



QlikView Technical Brief - AGGR.docx

crusader_
Partner
Partner

Hi John,

This line just count of unique  `From-To` for every  Carrier.

If you set it in dimension you just get a numbers and corresponding them Expressions.

For Example you have  a table:

carrierfrom-tonosales
deA3140
eeB2102
eeD6300
qeA1100
qeB420
qeC5120

Result table will be:

=aggr(count( distinct [from-to]),carrier)=sum(sales)
782
3240
2402
1140

Means that Carrier 'qe' has 3 distinct from-to codes, `ee` has 2, and `de` has 1.

Graphically Carrier will be Dimension, count(distinct ...) will be Expression

Hope it helps.

Andrei

Not applicable
Author

That doc uses long examples. I found a small and much better one for just AGGR(). Easy read. I am not sure if the example is 100% correct -

Aggr() function

I am trying to compare this to SQL (in SQL server). I did it like this (bus = carrier/flight, route = from-to) -

busroute
a1
a2
b1
b3
c1
c4
c6
c7
b11
d1
d2

-- Get the number of routes (call it RouteCount)

-- for each bus. Then, find the number of

-- unique RouteCounts. Find how many buses

-- serve each of these RouteCounts

select Routes, COUNT(Bus) as NumOfBuses

from

(

select bus, COUNT(route) as Routes

from rides

group by bus

) as Q1

group by Routes

The result of the above query is equivalent to AGGR()

RoutesNumOfBuses
22
31
41
maxgro
MVP
MVP

I think in your example the Qlik aggr  "aggr(COUNT(route), bus)"  is  the inner query

select bus, COUNT(route) as Routes

from rides

group by bus

bus  Routes

a      2

b      3

c      4

d      2

then you use the aggr as a calculated dimension

dim          aggr(COUNT(route), bus)                   

expr          count(distinct bus)



source data at the left

aggr top r

result bottom r
1.png