Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
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