topic Re: Help with Set Analysis and aggregation in QlikView

Help with Set Analysis and aggregation

Clever_Anjos — Tue, 23 Aug 2016 18:24:07 GMT

I have a dataset like below (excel file attached too)

I need to know (aggregated on [ID Som]) how many segments have a [ID Som] that have a segment = Silence of Duration between 22 and 66 (example) and also have segments = MUSIC of duration between 5 and 20.

In this sample I should have:

787 - 4
882 - 0 (It has SILENCE but hasn´t MUSIC)

ID Som	ID Segmento	Tipo Segmento	Duration
787	5923	VOICE	23,30
787	5924	SILENCE	8,23
787	5925	VOICE	1,84
787	5926	SILENCE	4,73
787	5927	VOICE	1,65
787	5928	SILENCE	29,22
787	5929	VOICE	13,34
787	5930	SILENCE	3,68
787	5931	VOICE	16,36
787	5932	SILENCE	51,37
787	5933	MUSIC	5,43
787	5934	SILENCE	4,78
787	5935	VOICE	2,61
787	5936	SILENCE	3,47
787	5937	VOICE	11,54
787	5938	SILENCE	10,27
787	5939	VOICE	9,09
787	5940	MUSIC	9,33
787	5941	SILENCE	9,77
787	5942	VOICE	24,22
882	6640	VOICE	68,25
882	6641	SILENCE	53,06
882	6642	VOICE	7,69
882	6643	SILENCE	10,00
882	6644	VOICE	6,35
882	6645	SILENCE	8,24
882	6646	VOICE	13,62
882	6647	SILENCE	6,91
882	6648	VOICE	69,60

Re: Help with Set Analysis and aggregation

sunny_talwar — Tue, 23 Aug 2016 18:44:33 GMT

What ID Segmento are getting included here?

Re: Help with Set Analysis and aggregation

Clever_Anjos — Tue, 23 Aug 2016 18:45:54 GMT

it´s the "id" of each row

Re: Help with Set Analysis and aggregation

sunny_talwar — Tue, 23 Aug 2016 18:56:41 GMT

but how did you get 4 for 787? I am having difficulty in understanding how you got that number.

Re: Help with Set Analysis and aggregation

Clever_Anjos — Tue, 23 Aug 2016 19:06:53 GMT

It´s the "count of ok"

ID Som	ID Segmento	Tipo Segmento	Duracao Segmento	Check
787	5924	SILENCE	8,23	outside threshold < 22
787	5926	SILENCE	4,73	outside threshold < 22
787	5928	SILENCE	29,22	ok between 22 and 66
787	5930	SILENCE	3,68	outside threshold < 22
787	5932	SILENCE	51,37	ok between 22 and 66
787	5933	MUSIC	5,43	ok between 5 and 20
787	5934	SILENCE	4,78	outside threshold < 22
787	5936	SILENCE	3,47	outside threshold < 22
787	5938	SILENCE	10,27	outside threshold < 22
787	5940	MUSIC	9,33	ok between 5 and 20
787	5941	SILENCE	9,77	outside threshold < 22

Re: Help with Set Analysis and aggregation

othniel2014 — Tue, 23 Aug 2016 19:17:34 GMT

882 - 0...It is not? ---> 882 - 1

882

6641

SILENCE

53.06

Re: Help with Set Analysis and aggregation

Clever_Anjos — Tue, 23 Aug 2016 19:20:53 GMT

No, because it has no MUSIC segment between 5 and 20

Re: Help with Set Analysis and aggregation

sunny_talwar — Tue, 23 Aug 2016 19:33:19 GMT

May be this:

=Count(DISTINCT {<[ID Som] = {"=(Count({<[Tipo Segmento] = {'SILENCE'}, [Duracao Segmento] = {'>=22<=66'}>}[Duracao Segmento]) > 0 and

Count({<[Tipo Segmento] = {'MUSIC'}, [Duracao Segmento] = {'>=5<=20'}>}[Duracao Segmento]) > 0)"}>*

(<[Tipo Segmento] = {'SILENCE'}, [Duracao Segmento] = {'>=22<=66'}>+<[Tipo Segmento] = {'MUSIC'}, [Duracao Segmento] = {'>=5<=20'}>)} [ID Segmento])

Re: Help with Set Analysis and aggregation

Clever_Anjos — Tue, 23 Aug 2016 19:42:13 GMT

You are the GUY!

Re: Help with Set Analysis and aggregation

Clever_Anjos — Tue, 23 Aug 2016 19:45:19 GMT

While this I´ve reached this "ugly" expression too

Sum(

Aggr(

If(

Count(if(([Duracao Segmento]>=5 and [Duracao Segmento]<=20 and [Tipo Segmento]='MUSIC'), [ID Segmento])) > 0 and

Count(if(([Duracao Segmento]>=22 and [Duracao Segmento]<=66 and [Tipo Segmento]='SILENCE'), [ID Segmento])) > 0 ,

Count(if(([Duracao Segmento]>=5 and [Duracao Segmento]<=20 and [Tipo Segmento]='MUSIC'), [ID Segmento])) + Count(if(([Duracao Segmento]>=22 and [Duracao Segmento]<=66 and [Tipo Segmento]='SILENCE'), [ID Segmento])) ),

[ID Som]

)