topic Re: Qlikview associations produce extra null rows in QlikView

Qlikview associations produce extra null rows

sir_ogrim — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Qlikview associations produce extra null rows

Peter_Cammaert — Thu, 28 Sep 2017 15:39:07 GMT

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...

Re: Qlikview associations produce extra null rows

oscar_ortiz — Thu, 28 Sep 2017 19:21:46 GMT

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

Re: Qlikview associations produce extra null rows

sir_ogrim — Mon, 02 Oct 2017 06:30:14 GMT

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!

Re: Qlikview associations produce extra null rows

sir_ogrim — Mon, 02 Oct 2017 06:33:05 GMT

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