Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Read about Qlik’s Response to COVID-19 Read the Letter, Join the Group.
New Contributor II

Qlikview associations produce extra null rows

Hi! I'm just wondering if I'm doing something wrong, or if this is really how things are supposed to be in Qlikview.

When Qlikview Associates two tables based on a common field name, it seems to produce null rows in one of the tables if one table contains data in the associated field that is not present in the other table.

Here's a simple example:

Employees:
LOAD * INLINE [
EmployeeID, Name, CountryID
1, Mike Tryson, USA
2, Alan Trying, UK
3, Tester Williams, USA
];

Countries:
LOAD * INLINE [
CountryID, CountryName
USA, United States of America
UK, United Kingdom
SWE, Sweden
NOR, Norway
];

The resulting employees table, when viewed in a Table Box, looks something like this:

1 | Mike Tryson     | USA

2 | Alan Trying     | UK

3 | Tester Williams | USA

- | -               | SWE

- | -               | NOR

I'm not too keen on having those last two null fields in the table. Now, I could of course check "Omit Rows Where Field is NULL" for the EmplyeeID field for this Table Box object, but is that the way one's supposed to do this? Am I doing something wrong?

Really thankful for any help! QlikView is very nice and the learning curve isn't too steep, but sometimes I'm not sure I'm doing things right.

1 Solution

Accepted Solutions
Highlighted

Re: Qlikview associations produce extra null rows

A Table Box shows you all possible combinations of values from the fields you added to the table box. In your case, two values from field  CountryID do not match any values in the other fields, so yes this is correct ouput.

You should consider a Table Box as a combined set of Listboxes, not as an ordinary table. Many times it will produce weird output but if you keep in mlind that the idea behind a Table Box is simply "show all possible combinations of values from different fields", you will be able to explain why a Table Box does what it does.

I mostly use Table boxes for debugging associations, and for listing value combinations where NULL values don't matter or are to be expected.

If you don't want to see any NULL values, suppress them in all fields where they are improper.

BTW a best practice in most documents is to reduce all dimension tables to those values that also occur in your fact tables. That would also solve your problem, but I'm not sure you want to go that far...

View solution in original post

4 Replies
Highlighted

Re: Qlikview associations produce extra null rows

A Table Box shows you all possible combinations of values from the fields you added to the table box. In your case, two values from field  CountryID do not match any values in the other fields, so yes this is correct ouput.

You should consider a Table Box as a combined set of Listboxes, not as an ordinary table. Many times it will produce weird output but if you keep in mlind that the idea behind a Table Box is simply "show all possible combinations of values from different fields", you will be able to explain why a Table Box does what it does.

I mostly use Table boxes for debugging associations, and for listing value combinations where NULL values don't matter or are to be expected.

If you don't want to see any NULL values, suppress them in all fields where they are improper.

BTW a best practice in most documents is to reduce all dimension tables to those values that also occur in your fact tables. That would also solve your problem, but I'm not sure you want to go that far...

View solution in original post

Highlighted
Partner
Partner

Re: Qlikview associations produce extra null rows

To add onto what pcammaert was saying, if you modify your script slightly you can reduce your data appropriately.

Employees:

LOAD * INLINE [

EmployeeID, Name, CountryID

1, Mike Tryson, USA

2, Alan Trying, UK

3, Tester Williams, USA

];

Countries:

LOAD

*

Where Exists( CountryID )

;

LOAD * INLINE [

CountryID, CountryName

USA, United States of America

UK, United Kingdom

SWE, Sweden

NOR, Norway

]

;

Good luck

Oscar

Highlighted
New Contributor II

Re: Qlikview associations produce extra null rows

Peter Cammaert: Thank you for an amazingly clear and concise response. And also, thank you for even telling me more than I thought I needed to know! Reducing the dimensions table to the values that actually appear in the fact table is obvious now that you pointed it out, but I actually didn't think of that. Thank you!

Highlighted
New Contributor II

Re: Qlikview associations produce extra null rows

Oscar Ortiz: Thank you! That was a really simple and useful function.