Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
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.
Hey Johannes,
Thanks a lot.... I'm going to implement this.
regards,
Marnix
Hello Marn,
Try this:
= Count({<Code={804}>} Code)
Mouhs
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.