Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
May be you need to use Peek within the If's true condition:
If(Date=Peek(Date), RangeSum(Peek('ErrCt'), 1), 1) As ErrCt
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;
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
You need a grouo by statement:
CT:
LOAD
Date,
Count(Message) As ErrCt
RESIDENT NewLog
WHERE Type = 'Error'
Group By Date;
thanks, Max. Can't believe I'm so dumb :{