Equivalent of SQL "HAVING" in chart expression

Thu, 18 Oct 2012 09:45:22 GMT

Hello,

I have the following data:

Call:
LOAD * INLINE [
ID, Date, Person
1, 01/01/2012, Bob
2, 02/01/2012, Bob
3, 03/01/2012, Bob
4, 04/01/2012, Bob
5, 01/01/2012, John
6, 02/01/2012, John
7, 03/01/2012, Michael
8, 04/01/2012, Michael
9, 05/01/2012, Michael
10, 06/01/2012, Michael
11, 07/01/2012, Michael
12, 01/01/2012, Peter
13, 06/01/2012, Peter
14, 07/01/2012, Karl
15, 01/01/2012, Bob
];

I would like to know how many Person are called, by number of calls.

With a chart, I can use a calculated dimension

=aggr(count(distinct ID), Person)

and the expression

count(distinct Person)

concat(distinct Person, ', ')

-->

Now, I would like to make a text area that gives me the number of person who are called a specific number of time.

Exemple:

"1 pers. (Karl) called only 1 time".

and

"2 pers. (Bob, Michael) called more than 3 times"

(bold is the result of an expression)

I want this text area to be updated when I change my selection of "Date".

Will result:

"2 pers. (Michael, Peter) called only 1 time".

and

"1 pers. (Bob) called more than 3 times"

In SQL, I guess I can do it with a "HAVING" key word.

Re: Equivalent of SQL "HAVING" in chart expression

Thu, 18 Oct 2012 09:52:21 GMT

LOL!

Writing the post made me find the solution!

count({$ <Person={"=count(distinct ID) = 1"}>} distinct Person) & '
' & concat({$ <Person={"=count(distinct ID) = 1"}>} distinct Person, ', ')

count({$ <Person={"=count(distinct ID) > 3"}>} distinct Person) & '
' & concat({$ <Person={"=count(distinct ID) > 3"}>} distinct Person, ', ')

topic Re: Equivalent of SQL "HAVING" in chart expression in QlikView

Equivalent of SQL "HAVING" in chart expression

Re: Equivalent of SQL "HAVING" in chart expression