topic Calculated dimension - Need help to understand one line script in QlikView

Calculated dimension - Need help to understand one line script

Fri, 20 Nov 2020 18:02:30 GMT

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.

Re: Calculated dimension - Need help to understand one line script

maxgro — Thu, 03 Apr 2014 19:05:59 GMT

graphically ?

make a chart with

dimension Carrier Name

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

QlikView Technical Brief - AGGR.docx

Re: Calculated dimension - Need help to understand one line script

crusader_ — Thu, 03 Apr 2014 19:19:02 GMT

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

Re: Calculated dimension - Need help to understand one line script

Thu, 03 Apr 2014 21:36:30 GMT

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

Re: Calculated dimension - Need help to understand one line script

maxgro — Thu, 03 Apr 2014 22:15:55 GMT

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