topic Joining 3 lists in QlikView

Joining 3 lists

Thu, 08 Mar 2012 15:23:56 GMT

I have 3 lists i have to combine.

In every list i add an extra field in the script to identify from which list the record comes.

For Example :

Table 1 :

Name T1

A 1

B 1

C 1

D 1

Table 2 :

Name Code T2

A 1 1

C 2 1

E 3 1

Table 3 :

Code T3

1 1

3 1

4 1

After my joins i get 1 table :

Table:

Name Code T1 T2 T3

A 1 1 1 1

B - 1 - -

C 2 1 1 -

D - 1 - -

E 3 - 1 1

- 4 - - -

Now here is my problem :

How can i get the records of Table 1 that have no relation with Table 2 and Table 3? (same for T2 or T3) so where the value of T1 is 1 and where T2 & T3 have no values.

Joining 3 lists

jvitantonio — Thu, 08 Mar 2012 15:39:24 GMT

What row wuold you like to see as result? 'B'?

Joining 3 lists

Thu, 08 Mar 2012 15:44:38 GMT

In my example yes.

But the best would be if I had 3 listboxs with for example

Listbox T1

That i can select 1 if i want the rows of table 1 and 0 if i want to exclude the rows in Table 1.

But in my table they are null values.

Joining 3 lists

Thu, 08 Mar 2012 15:54:09 GMT

so that i can choose if i want records from Table 1 and Table 2 but not Table 3

or Table 1 but not in Table 2 and Table 3.

Joining 3 lists

jonathandienst — Thu, 08 Mar 2012 16:31:56 GMT

Use NullAsValue T1, T2, T3 to make the null values selectable and then create listboxes for T1, T2, T3. Now you can select any combination of values and nulls from your data set.

To choose records in Table 1 and not in table 2 and 3, select 1 for T1, and the null (shows as a blank) in T2 and T3.

Regards

Jonathan

Joining 3 lists

Fri, 09 Mar 2012 07:49:54 GMT

So i joined 3 lists in 1 complete list

Then i used the NullAsValue as you proposed but it still doesnt work:

NULLASVALUE T1,T2,T3;
load T1,T2,T3
Resident CompleteList;

So whats wrong with my syntax? Its the first time I use NullAsValue

Re: Joining 3 lists

jvitantonio — Fri, 09 Mar 2012 08:30:35 GMT

Hi, here's the solution. I hope it helps.

Re: Joining 3 lists

Fri, 09 Mar 2012 08:42:41 GMT

Yep, I also discovered that i just had to put everything in an other table before it worked.

So its fixed.

But i still find it weird.

So in my CompleteList the Null values were like this : -

so i did

NewList:

load *, '1' as Test

resident CompleteList;

drop table CompleteList;

And the '-' values changed in 0 values just by copying the old table and to add a simple field. Really weird

because i never used a If(IsNull())

But it works , so Thanks!

Edit : I used :

Set NullValue = 0;