topic Re. :Re: Help using Count function in QlikView

Help using Count function

Sun, 06 Jun 2010 14:50:09 GMT

Hi guys,

I need to calculate the average of Parts that appear every month, (I should count also those parts that appears in more than 1 month)

In example:

Month PartNo

2010-01 Part1

2010-01 Part2

2010-01 Part3

2010-01 Part4 Total Count for 2010-01 => 4

2010-02 Part1

2010-02 Part10

2010-02 Part11

2010-02 Part12

2010-02 Part13

2010-02 Part14 Total Count for 2010-02 ==> 6

Total Count for 2010-01 + 2010-02 ==> 10

Average should be ==> 10/2 = 5

The problem is that using = Count( [Part No]) gives me 9, because Part1 appears twice.

How can I work it around?

Thanks in advance,

Aldo.

Help using Count function

Sun, 06 Jun 2010 17:16:24 GMT

hi aldo,

first concatenate your month and part no in your script itself somewhat like this

tab1:

load Month&','&PartNo as column;

load *inline[

Month PartNo

2010-01 Part1

2010-01 Part2

2010-01 Part3

2010-01 Part4

2010-02 Part1

2010-02 Part10

2010-02 Part11

2010-02 Part12

2010-02 Part13

2010-02 Part14

];

and then use

load count(mid(column,index(column,',')+1)) as mycount resident tab1;

thanks

Help using Count function

Mon, 07 Jun 2010 04:49:40 GMT

Hi Aldo!

How about using = Count( distinct [Part No]).

That should work..

-Lester

Help using Count function

Mon, 07 Jun 2010 10:50:42 GMT

Concatenating Month with PartNo was the solution I was looking for.

Thanks,

Aldo.

Help using Count function

Mon, 07 Jun 2010 10:51:49 GMT

Hi Lester,

I tried Count(Distinct...), but it didn't work.

Thanks,

Aldo.

Re. :Re: Help using Count function

Mon, 07 Jun 2010 11:00:32 GMT

Qlikview use an associative model to store data , every occurence of each field is stored only one time , so when we use count ( partno ) , the bahavior is like count(distinct partno). try also to add a column with 1 as value for all rows and do a sum() to this field .

ex:

load month ,

partno ,

1 ;

hope that it will help

Re. :Re: Help using Count function

Mon, 07 Jun 2010 14:43:48 GMT

Thanks for your explanation.

Aldo.