Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

count number of days with Error

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

err.jpg

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.

1 Solution

Accepted Solutions
MVP
MVP

Re: count number of days with Error

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;

    

5 Replies

Re: count number of days with Error

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

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

MVP
MVP

Re: count number of days with Error

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;

    

Not applicable

Re: count number of days with Error

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

Re: count number of days with Error

You need a grouo by statement:

CT:

LOAD

  Date,

  Count(Message) As ErrCt

  RESIDENT NewLog

  WHERE Type = 'Error'

Group By Date;

Not applicable

Re: count number of days with Error

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

Community Browser