Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for
Did you mean:
Not applicable

## Joining 3 lists

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.

1 Solution

Accepted Solutions
Luminary Alumni

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

7 Replies
Luminary Alumni

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

Not applicable
Author

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.

Not applicable
Author

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.

Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

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;
Resident CompleteList;

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

Luminary Alumni

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

Not applicable
Author

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:

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;

Community Browser