Calculated dimension - Need help to understand one line script

[#1]

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.

[maxgro]

graphically ?

make a chart with

dimension     Carrier Name

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

QlikView Technical Brief - AGGR.docx

[crusader_]

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:

carrier	from-to	no	sales
de	A	3	140
ee	B	2	102
ee	D	6	300
qe	A	1	100
qe	B	4	20
qe	C	5	120

Result table will be:

=aggr(count( distinct [from-to]),carrier)	=sum(sales)
	782
3	240
2	402
1	140

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

[#4]

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

bus	route
a	1
a	2
b	1
b	3
c	1
c	4
c	6
c	7
b	11
d	1
d	2

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

Routes	NumOfBuses
2	2
3	1
4	1

[maxgro]

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