topic How to count values in QlikView

How to count values

Mon, 30 May 2011 20:41:37 GMT

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 ?

Re: How to count values

Anonymous — Tue, 31 May 2011 01:12:35 GMT

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.

How to count values

Tue, 31 May 2011 07:40:05 GMT

Hey Johannes,

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

regards,

Marnix

How to count values

Tue, 31 May 2011 08:14:38 GMT

Hello Marn,

Try this:

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

Mouhs

Re: How to count values

Tue, 31 May 2011 19:47:11 GMT

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.