4 Replies Latest reply: Jun 1, 2011 3:48 AM by Mike Timmers RSS

    How to count values

      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
          Johannes Sunden

          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

              Hey Johannes,

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

               

              regards,

              Marnix

              • Re: How to count values

                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.

              • How to count values

                Hello Marn,

                Try this:

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

                Mouhs