QlikView is unioning my tables when I don't want it to

[#1]

I am trying to load 2 separate tables with the same primary key but each other field in the tables are different. The count of the primary key when I load the first table alone is 7909. When I load the 2nd table it is 23266. When I load the two tables (ONLY those two tables from QVDs, no manipulation whatsoever) I get 24553... I was weirded out by the result so I did some investigation and ran a sql query unioning the two select statements I used to extract the QVD's and, lo and behold, I got the same number: 24553.

At first I thought that the larger table may have included all key values in the first table I loaded, but after running a few queries I found out that there are at least 1000 values that are not included in that first table. Why is qlikview Unioning my tables? This is very frustrating as I need these values to stay to each their own.

Any help on this would be greatly appreciated.

Thanks

[rwunderlich]

We have a saying in QV. "What's the best way to count keys? Don't."

There are ambiguities and sometimes strange results in counting keys because of the way QV stores data. Qv counts values, and those keys in one table but not the other will increase the count. Bottom line is that counting key fields in not reliable and you should establish separate counter fields on the specific tables where you want them.

-Rob

[jagan · Accepted solution]

Hi,

We should not count the key values, internally keys in two different tables are concatenated or union is happen.  So never take count of Key fields.  Instead you load the same field again with different name in both the tables or arrive a flag like below

Using Flag

Table1:

LOAD

*,

'Table1' AS Flag1

FROM Table1;

Table2:

LOAD

*,

'Table2' AS Flag2

FROM Table2;

Now if you want to get key count in table 1 use

=Count({<Flag1={'Table1'}>} DISTINCT Key)

to get key count in table 2 use

=Count({<Flag2={'Table2'}>} DISTINCT Key)

Hope this helps you.

Regards,

Jagan.

[rwunderlich]

Jagan,

I don't understand your example. Why count the Key (which you yourself said was a bad practice). Why not just count(Flag1) or count(Flag2)?

-Rob

[jagan]

Hi Rob,

We can also use Count(Flag1) or Count(Flag2) if the key is a primary key, but if the key values are not unique then we can use this expressions

=Count({<Flag1={'Table1'}>} DISTINCT Key)

=Count({<Flag2={'Table2'}>} DISTINCT Key)

In both the cases this expressions work, because we are using Flag fields.

Regards,

jagan.

[rwunderlich]

I understand. Interesting technique.

-Rob