Comment travailler avec les itérations dans Qlikview ? (fichier exemple)

0
53
quivalent of SQL « HAVING » in chart expression

 

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)

or

concat(distinct Person, ‘, ‘)

–>

2012-10-18_113328.png

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 ».

2012-10-18_113833.png

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.

https://community.qlik.com/t5/QlikView-Creating-Analytics/Equivalent-of-SQL-quot-HAVING-quot-in-chart-expression/td-p/427173

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

LEAVE A REPLY

Please enter your comment!
Please enter your name here