count number of days with Error

[#1]

Hi,I have a table that looks like this (only "Error" type messages shown)

I want to count the number of errors each day in the script. (e.g. 8/1/15 should be "6", 8/2/15 should be "2".The parent table is already loaded (NewLog)

I tried

For i = 0 to NoOfRows(NewLog) - 1

          CT:

          LOAD

               if(Date=Peek(Date,-1),ErrCt+1,1) As ErrCt //If the date is the same, increment ErrCt, else set to 1

          Resident (NewLog)

     WHERE

     Type = 'Error'

Next

but that didn't work at all.

I'm embarrassed to be asking, as I'm sure this is simple, but I can't figure it out. Thanks.

[sunny_talwar]

May be you need to use Peek within the If's true condition:

If(Date=Peek(Date), RangeSum(Peek('ErrCt'), 1), 1) As ErrCt

[maxgro · Accepted solution]

From what I understand, you have a NewLog table with Date and Type and you want to count, by date, the type with value 'Error'

load

     Date,

     count(Type)

resident

     NewLog

where

     Type = 'Error'

group by

     Date;

[#4]

Well, that completes, but I get errors for the "For i = 0 to NoOfRows..." and"next", and ErrCt is always "1".

What I want is a table that looks like this:

Date        ErrCt

8/1/15        6

8/4/15        2

8/5/15        1

etc. I tried adding "Date" as a field in my LOAD statement, but that didn't work either.

I also tried:

For each Date In NewLog

CT:

LOAD

  Count(Message) As ErrCt

  RESIDENT NewLog

  WHERE Type = 'Error';

Next ;

which completes without error, but gives me a single value of 234 (number of days in NewLog)

then I thought, stupid, put the Date in the logic! so  I tried

For each Date In NewLog

CT:

LOAD

  Date,

  Count(Message) As ErrCt

  RESIDENT NewLog

  WHERE Type = 'Error';

Next ;

But that throws up an error Invalid Expression

[sunny_talwar]

You need a grouo by statement:

CT:

LOAD

  Date,

  Count(Message) As ErrCt

  RESIDENT NewLog

  WHERE Type = 'Error'

Group By Date;

[#6]

thanks, Max. Can't believe I'm so dumb :{