Counting a particular number

[#1]

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???

[bbi_mba_76]

Hi,

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

[CELAMBARASAN · Accepted solution]

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

[tresesco]

May be like attached sample. PFA

[Gysbert_Wassenaar]

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

[#6]

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)

[#7]

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

Kind Regards,

Waqas
Karachi, Pakistan.

[Anonymous]

you can have a simple expression like this:

=sum(CountZeros)

when you move some of your logic to the script:

T:

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;