How does Qlik know which table I want to evaluate in my chart expression

[#1]

Probably a basic questions, but I need to figure this out to understand how Qlik works. Any help is appreciated!

Consider the following two tables:

Table A

Col1
1
2
3
4

Table B

Col1
1
2
5

Since the two tables both have a column with the name 'Col1', the two tables will be joined in Qliks data model.

Let's say I have a chart expression:

Count(Col1)

Questions:

1) What will be the resulting value of the chart expression?

2) How does Qlik decide which table to count?

3) How can I qualify the table column name in the chart expression (e.g. by adding a table name) so that I'm in control of which table is counted?

Thanks!

[swuehl · Accepted solution]

Good question!

Answer is:

Don't Count(KEYFIELD) !

Because QV can't determine which table you would like the count to be performed in and you can't specify / control it.

The results are non-deterministic.

(You can do a Count(DISTINCT KEYFIELD) without any problems though)

[#3]

Hi Tor,

Under the software, QlikSense count the TOTAL of register. So, in your example, the count must be '7' and the sum must be 18.

Best regards.

[#4]

Thanks for two interesting answers

To get a better understanding, I tried it in practice. See screenshot below.

It seems that swuehls "Don't Count(Keyfield)" is a good recommendation, because the answer I got didn't make much sense to me. The same goes for Sum(Keyfield), and I assume other aggregation functions.

Using the distinct keyword, the functions seem to count or sum the distinct keyfield values from both tables, combined. So, without the distinct keyword, I would have expected to get the result that Juan suggested, but I got the same results as with the distinct keyword. Which is what doesn't make any sense to me, and which is why I guess I'll stick to swuehls recommendation going forward.

Trying to be constructive: To count the keyfield from table A, I added a new column (Col2) with nonsense values, and stuck a condition to ignore nulls in Col2 into the expression. I guess there is some kind of outer join going on, because this did give the expected result, 4.

If anyone knows of any 'official' documentation about what is going on, it'd be interesting to see.

Thx guys

[swuehl]

Have a look at Henric's blog post:

Count or Count distinct?

As you already did, a common way to count the records of a key field in a specific table is to count another field in that table, that shows a value for every key field value.

With regard to the results when counting a key field: The results are non deterministic, e.g. might depend on the load order of your tables.