topic Re: Connecting Tables, Null() Values are missing in QlikView

Connecting Tables, Null() Values are missing

Thu, 04 Feb 2016 11:45:18 GMT

Hello,

I am pretty much a newbie to QlikView and I was hoping that someone could help me with the issue I have right now.

I created 3 different tables, each one of them has the column "id" , so that this is the key with which they are connected. Viewing the table structure everything looks fine to me.
Now, I would expect that Qlik VIew joins the tables, so that for each id, that is missing in one table, NULL would be assigned for the missing values.

It seems as if this is not the case. I have a listbox with a field which is not existent for every user, so I want to have another option in this listbox "unknown" for all NULL() . I used an if expression, which would be:
=if(isNULL(field_a),'unknown',field_a)

I also tried the command "JOIN" in the script and it behaved the same way.

I found some articles about MAPPING, but this would assign a value to existent null values caused by a join, correct? In my case, I want them to stay null, I just want to be able to identify them.

I really hope my problem is clear and that someone is able to help 😃

Thanks!

Jaqueline

Re: Connecting Tables, Null() Values are missing

Thu, 04 Feb 2016 18:31:01 GMT

I think I already figured something out.
What I woul need ideally, is an outer Join on all three tables.
Is that possible?
So far, I could only join 2 at a time, so that values keep missing.

I really would appreciate your help.

Thank you!

Re: Connecting Tables, Null() Values are missing

swuehl — Thu, 04 Feb 2016 18:38:28 GMT

Could you post some sample lines of data and the table you expect to get?

If you want to JOIN tables in the script, you can use an

OUTER JOIN (Table) LOAD ...

Re: Connecting Tables, Null() Values are missing

Fri, 05 Feb 2016 08:55:11 GMT

Thanks for your reply!

What I tried to solve the Null issue in the listbox is:

SET Nullvalue = '';

NULLASVALUE *;

And while I did this, I noticed that the outer join only works for 2 tables at a time. (It worked for the missing values but only for one join) Maybe it's worth mentioning, that I try to connect data out of three different databases, so that's why I can't join them in SQL directly.

What I did so far is :

CONNECT TO [Database A];

TABLE A:

select
id,

a_country,

a_name,

...

from ...
;
DISCONNECT;

CONNECT TO [Database B];

OUTER JOIN
TABLE B:

select

id,

b_city,

b_age,

...

from...

;

DISCONNECT;

CONNECT TO [Database C]

OUTER JOIN
TABLE C:

select

id,

c_language,

c_level,

....

from...

;

DISCONNECT;

What I would like to have is:

(note: this is not how my data looks like, it's just to make the problem clear )

At the moment , it seems like this is not my outcome.
For example, there would be no Null value for 7 in a_country and a_name

At the very beginning, I had no join at all. I know that I can use OUTER JOIN(NAME OF TABLE) to specify the table it should join with, because otherwise it is just joining the previous one.

How can I join them all three together so that every id has a value or null() for each field?

I hope my problem got clear and I kind of think there should be a simple solution for this, but I couldn't find it yet.

Thank you!

Re: Connecting Tables, Null() Values are missing

Fri, 05 Feb 2016 16:28:32 GMT

I think I narrowed it down to the main issue:
I have "missing values" , which means that there is not even a record for this, but I need a null() or something else to make it selectable in the listbox (I know, that I can't select null(), but I can use the trick I used before and set the nulls with a value.

I'd appreciate your help

Jaqueline

Re: Connecting Tables, Null() Values are missing

Tue, 09 Feb 2016 12:16:16 GMT

Anyone? I'd be very thankful for any help

Re: Connecting Tables, Null() Values are missing

swuehl — Tue, 09 Feb 2016 14:13:38 GMT

Still not sure what you are talking about. Looking at your image,

LOAD * INLINE [

ID, ValueA

1, A

2, B

];

OUTER JOIN

LOAD * INLINE [

ID, ValueB

2, C

3, D

];

OUTER JOIN

LOAD * INLINE [

ID, ValueC

4, E

4, F

];

it seems it replicates your expected result. Or maybe I just don't get what you are expecting here.

If you are missing complete records, look into

Generating Missing Data In QlikView

and the referenced tech doc.