Joining 3 lists

[#1]

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.

[jvitantonio]

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

[#3]

In my example yes.

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

Listbox T1

0

1

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.

[#4]

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.

[jonathandienst]

Hi

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

[#6]

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

[jvitantonio · Accepted solution]

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

[#8]

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;