Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

wrong key field count

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

1 Solution

Accepted Solutions
mov
Esteemed Contributor III

Re: key field count depending on script table load order

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

14 Replies
Not applicable

Re: key field count depending on script table load order

hi

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

here field B is not primary key.

Not applicable

Re: key field count depending on script table load order

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"

mov
Esteemed Contributor III

Re: key field count depending on script table load order

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

Not applicable

Re: key field count depending on script table load order

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

sujeetsingh
Honored Contributor III

Re: wrong key field count

Why dont you try for joins ????

Not applicable

Re: wrong key field count

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.

Not applicable

Re: key field count depending on script table load order

hi

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

Not applicable

Re: key field count depending on script table load order

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.

Not applicable

Re: wrong key field count

hi

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)