topic AW:Count ID per Value in script in QlikView

Count ID per Value in script

Tue, 01 Dec 2009 21:52:00 GMT

Dear all,

another month, another try [:)]

I need to count out the number of IDs during a value. This should be done in the script, what looks like this:

Main.Data:
Load
 ID AS CountIDMin,
 Start,
 End,
 Date
Resident Original.Data;

LEFT JOIN (Main.Data)
INTERVALMATCH (Datum_time)
LOAD
 Start,
 End
RESIDENT Original.Data2;


Final.table:
Load
 CountIDMin,
 Start As StartMin,
 End as EndMin,
 Date as DateMin,
 Datum_time
RESIDENT Main.Data;

Final.Count:
Load
 Date_time,
 Count(CountIDMin) AS Count
Resident Main.Data
Group by Datum_time;

Unfortunately the result in a simple table in the layout looks like the attached jpeg. Instead of the expected result "8" the column Count shows "17".

Maybe there is something wrong in the script?

AW:Count ID per Value in script

Tue, 01 Dec 2009 22:11:45 GMT

Try count(distinct ...)

AW:Count ID per Value in script

Thu, 03 Dec 2009 23:32:07 GMT

Thanks for the quick reply Pascal. This was my foremost thought too, because 17 would be the correct result if I take the whole raw dataset instead of a subtotal. Unfortunately the result remains the same, even using the distinct function.

In my original data 2 synthetic keys will be created in the script. Maybe this is a problem? (Even I have ben working with QV since some month now, I am still not familiar with all its functions... )

AW:Count ID per Value in script

Fri, 11 Dec 2009 20:57:42 GMT

you are right Pascal - it of course distinct. Also I just avoid using synth. keys and it works fine now.