wrong key field count

[#1]

Hi,

I have found that a chart result (field count) dipends on script load order. Here is the simple case I built to isolate the problem. The script is:

table_ab:
load * Inline [
A, B
10, 20
12, 22
12, 23
17, 23
18, 26
19, 26
];
table_bc:
load * Inline [
B, C
20, 31
20, 32
25, 33
25, 34
26, 35
26, 36
];

As you can see there are two tables connected by field B.

If I try to count B using B as dimension I get:

B	Count(B)
20	1
22	1
23	2
25	1
26	2

The first strange thing is that values 20 anf 25 are counted once even if, in table_bc, they appear twice. I'm not sure about value 26, maybe count should be 4?

The second strange thing is that if I invert table load order (table_bc loaded before table_ab) the count of B changes:

B	Count(B)
20	2
22	1
23	1
25	2
26	2

Now it seems that the count is "made primary" on table_bc (the first one read) since valie 20 has count equal to 1 and value 23 has count equal to 2.

So we have two (connected) strange things:

1) count of key field seems to have a "primary" table,

2) final result depends on table load ordere

Can anybody explain me this behaviour?

Thanks in advance to all replies.

Filippo

[er_mohit]

try this in your script

table_ab:

load * Inline [

A, B

10, 20

12, 22

12, 23

17, 23

18, 26

19, 26

];

Concatenate (table_ab ) Add

table_bc:

load * Inline [

B, C

20, 31

20, 32

25, 33

25, 34

26, 35

26, 36

]

where  exists (B);

in pivot table take dimension B

and in expression

count(B)

hope it helps

[#3]

Thanks to everybody for the possible solutions to the problem.

However what I would like to know is not how to solve the problem, but to understand if there is some rule which says: "you cannot make charts with the same key field as both dimension and expression" or something similar.

Is there something in the documentation abiut that?

Filippo

[Anonymous · Accepted solution]

Filippo,

In the eariler QV versions (maybe up to QV7, but I don't have that's old versions installed now), if you try to create expression count(B) where B is a key field, the expression dialogue made this "B" red, and the mesage was something like "dangerous ffield".  This warning was removed later, but it didn't change the fact.  There could be multiple way to define what count(B) means.

Notice, that if you create a list box for a key field, you cannot use property "Show Frequency" - it is for the same reason.

Regards,

Michael

[swuehl]

Filippo,

just adding to what Michael already said:

I've read this statement from time to time, but can't remember where and searching the Help file and reference manual haven't shown a reference neither.

Only thing I've found (but I trust Henric that he knows QV well enough):

http://community.qlik.com/message/265064

Regards,

Stefan

[#6]

Thank you, foolowing your link and searching thr forum for 'count "key field"' I have found some refernces saying that aggregation functions may give wrong results if applied to key fields.

On page 367 of reference manual (V 11, paragraph: Frequency Information in Associating Fields) there is someinformation about that.