How to count values

[#1]

I had some values in a database table:

datetime   - location - code

01-01-2011 00:00:02  - 12 - 301

01-01-2011 00:00:04  - 14 - 804

01-01-2011 00:00:06  - 12 - 804

01-01-2011 00:0012   - 14 - 804

01-01-2011 00:00:20  - 12  -804

01-01-2011 00:00:22  - 11 - 301

01-01-2011 00:00:25  - 12 - 804

01-01-2011 00:00:31  - 11 - 804

01-01-2011 00:00:34  - 12 - 301

code 301 is like a reset command, I want to count de 804 codes between two 301 code.

I want to create these answer

datetime  - location - maxcount

01-01-2011 00:00:34 - 12 - 3

01-01-2011 00:00:55 - 14 - 5

How can I count these maxcount values in Qlikview ?

[Anonymous]

Hey,

As usual, there are many ways of doing these things in the script.

One approach, if you want to count the occurences of Code=804 for every Location, while pulling the maxtime is to do like this:

Temp:

Load

          date#(DateTime, 'DD-MM-YYYY hh:mm:ss') as DateTime,

          Location,

          Code;

LOAD * INLINE [

DateTime, Location, Code

01-01-2011 00:00:02, 12, 301

01-01-2011 00:00:04, 14, 804

01-01-2011 00:00:12, 14, 804

01-01-2011 00:00:06, 12, 804

01-01-2011 00:00:20, 12, 804

01-01-2011 00:00:22, 11, 301

01-01-2011 00:00:25, 12, 804

01-01-2011 00:00:31, 11, 804

01-01-2011 00:00:34, 12, 301

];

Load

          Date(Max(DateTime), 'DD-MM-YYYY hh:mm:ss') as MaxDateTime,

          Sum(if(Code=804,1,0)) as MaxCount,

          Location

resident Temp

Group by Location;

Do note though, that this solution does not take into consideration the fact that you might have Location codes that have not yet come to a close Code (301).

In a more flexible solution I would sort the table based on Location and DateTime and check for the 301 break points and flag them using the Peek() function. Then I would count the occurences of Code=804 between those flags.

[#3]

Hey Johannes,

Thanks a lot.... I'm going to implement this.

regards,

Marnix

[#4]

Hello Marn,

Try this:

= Count({<Code={804}>} Code)

Mouhs

[#5]

Johannes,

I need the more flexible solution, now i count only the 804 and when the location has a 301 code i would reset the maxcount en count again. I want something like this (for example only location 12)

datetime  - location - maxcount

01-01-2011 00:00:34 - 12 - 3

02-01-2011 00:00:34 - 12 - 43

03-01-2011 14:00:01  -12 - 21

04-01-2011  16:00:01 -12 - 11

The maxcount are the 804 counts between two 301 code flags.  I dont know how to use the Peek() function.

Who can help me....

regards.