topic Re: Counting a particular number in QlikView

Counting a particular number

Mon, 07 Oct 2013 12:27:43 GMT

Hello,

I have a table which list values as follow:

Name Time Value

A 01:00 57

A 02:00 34

A 03:00 0

A 04:00 24

A 05:00 0

A 06:00 0

A 07:00 65

Now i'm stuck in getting to count the occurrences of zeros. The answer should be 2 , because the 0 at 06:00 is not required to be part of total since its predecessor is also a 0 and has been counted in the total.

How to do that in an expression???

Re: Counting a particular number

bbi_mba_76 — Mon, 07 Oct 2013 12:42:20 GMT

Hi,

I think you could solve it in the script, joining the table with the previous time and checking the 0 values.

Re: Counting a particular number

CELAMBARASAN — Mon, 07 Oct 2013 12:44:22 GMT

Hi,

You can do it with the help of script

LOAD *, If(Previous(Name) = Name and Previous(Value) <> 0 and Value = 0, 1, 0 ) AS ZeroFlag Inline

[Name,Time,Value

A,01:00,57

A,02:00,34

A,03:00,0

A,04:00,24

A,05:00,0

A,06:00,0

A,07:00,65];

You can simply use Sum(ZeroFlag) in chart expressions.

Hope that helps

Re: Counting a particular number

tresB — Mon, 07 Oct 2013 12:46:22 GMT

May be like attached sample. PFA

Re: Counting a particular number

Gysbert_Wassenaar — Mon, 07 Oct 2013 12:48:38 GMT

Try count({<Value={0}>}aggr(if(Above(only(Value)),only(Value)),Name,Time))

Re: Counting a particular number

Mon, 07 Oct 2013 12:49:48 GMT

I think better option is to include a Field in Script called Dif or something like dat like

TABLE:

LOAD *,Value - Peek(Value) as Dif;

LOAD * INLINE [

Name, Time, Value

A, 01:00, 57

A, 02:00, 34

A, 03:00, 0

A, 04:00, 24

A, 05:00, 0

A, 06:00, 0

A, 07:00, 65

];

in expression

Count({<Value={0},Dif={*}-{0}>} Value)

Re: Counting a particular number

Mon, 07 Oct 2013 13:01:42 GMT

Thanks everyone for resolving the issue. Have a great day.

Kind Regards,

Waqas
Karachi, Pakistan.

Re: Counting a particular number

Anonymous — Mon, 07 Oct 2013 13:03:49 GMT

you can have a simple expression like this:

=sum(CountZeros)

when you move some of your logic to the script:

load * inline [

Name,Time,Value

A,01:00,57

A,02:00,34

A,03:00,0

A,04:00,24

A,05:00,0

A,06:00,0

A,07:00,65

];

left join (T)

load

Name,Time,

Value as ValueTmp,

if(Value=0 and peek(ValueTmp)<>0,1,0) as CountZeros

resident T

order by Name asc, Time asc;

drop field ValueTmp;