topic wrong key field count in QlikView

wrong key field count

Mon, 07 Jan 2013 08:57:15 GMT

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

Re: key field count depending on script table load order

Mon, 07 Jan 2013 09:02:31 GMT

i think if you connected two table then one field must be common as a primary key.

here field B is not primary key.

Re: key field count depending on script table load order

Mon, 07 Jan 2013 09:09:09 GMT

Hi vishwaranjan, thank you for your reply.

Maybe I don't understand well: what do you mean by "primary" key? The two tables are connected by common field B as can be see in table viewer where the two tables are connected by B indicated as [Key], but not "primary"

Re: key field count depending on script table load order

Anonymous — Mon, 07 Jan 2013 19:06:03 GMT

It is "illegal" to use count(B) when B is a key field. You can use count (distinct B) - it will be 1 in each row.

Or, create duplicate keys in each table, say B as B1 in one table, and B as B2 in another table, and use count(B1) and count(B2)

Regards,

Michael

Re: key field count depending on script table load order

Tue, 08 Jan 2013 06:49:57 GMT

Hi Michael, thank you for your reply.

Do you have some reference to documentation about this "illegality" (QV manual, other comunity posts, ...)?

I would like to have more deatils about what is illegal: reading your answer it seems that you can never user key fields in expressions!

Thank you,

Filippo

Re: wrong key field count

sujeetsingh — Tue, 08 Jan 2013 07:14:34 GMT

Why dont you try for joins ????

Re: wrong key field count

Tue, 08 Jan 2013 07:26:13 GMT

Hi, at the moment I'm not trying to solve the problem (i.e. find the correct value for count(B)), I want to understand why the actual formulation is not correct, in order to avoid problems in the future.

Re: key field count depending on script table load order

Tue, 08 Jan 2013 07:57:37 GMT

i think,the concept is that when you join two table then one field must be common and that field must be primary key,

because on the basis of primary key we join two tables, so we make that field as a primary key of parent table,and make same field as a foregien key of of child table

Re: key field count depending on script table load order

Tue, 08 Jan 2013 08:17:11 GMT

Hi, I agree with you if you speak about "tradional" systems, but I think that in QlikView there isn't any concept of "primary key", "foreign key", "parent table" or "child table". All keys are simply keys and all tables are simply tables, with no hierarchy (of course, if I'm wrong, please correct me). I think that this "lack" of hierarchy is one of the strengths of QV.

Re: wrong key field count

Tue, 08 Jan 2013 08:56:33 GMT

table_ab:

load * Inline [

A, B

10, 20

12, 22

12, 23

17, 23

18, 26

19, 26

];

Concatenate

table_bc:

load * Inline [

B, C

20, 31

20, 32

25, 33

25, 34

26, 35

26, 36

];

the output show like this

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

write in expression count(B)

Re: wrong key field count

er_mohit — Tue, 08 Jan 2013 08:56:51 GMT

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

Re: wrong key field count

Tue, 08 Jan 2013 12:34:25 GMT

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

Re: key field count depending on script table load order

Anonymous — Tue, 08 Jan 2013 13:19:27 GMT

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

Re: wrong key field count

swuehl — Tue, 08 Jan 2013 14:42:16 GMT

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

Re: wrong key field count

Thu, 10 Jan 2013 17:52:17 GMT

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.