Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
Hi Aldo!
How about using = Count( distinct [Part No]).
That should work..
-Lester
Concatenating Month with PartNo was the solution I was looking for.
Thanks,
Aldo.
Hi Lester,
I tried Count(Distinct...), but it didn't work.
Thanks,
Aldo.
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
Thanks for your explanation.
Aldo.