Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Where not exist

Hello everyone,

Currently, I have a script in with these 2 blocks.

Table1:
Load
key as B
*;
Select

.. from TableA;

Table2:
Load
key as B
*;
Select

.. from TableA;

With different rules in the "select" but both tables have the same structure.

I want to keep all the lines that are in Table1 and add the lines from Table2 for which "key" is not in Table1.

I wanted to use "where not exists". But since My Load for the second table is based on a "select", I cannot directly use it. So I wanted to add a third bloc like  LOAD * resident Table2 where not exits (key,key)  But I don't know how I can specify that I only want to exclude the "key" that are already in Table1 (without taking into account the Table2 which will be load before ).

Thank you for you help

Have a good day

Laura

8 Replies
luciancotea
Specialist
Specialist

First, tables will be concatenated automatically because they have the same structure. Assuming this is what you want, you can add a preload to the second table:

Table1:
Load
key as B
*;
Select

.. from TableA;



Table2: // actually this will concatenate to Table1, so there;s no point in naming

Load *

where not exits (B)

;

Load
key as B
*;
Select

.. from TableA;

swuehl
MVP
MVP

You can probably do the filtering directly on the DB server using SQL, but if you want to use QV scripting:

Table2:
Load
key as B,
*

WHERE NOT EXISTS(B, key);
Select

.. from TableA;


Take care that EXISTS() will also consider keys / B values loaded so far in Table2 load.

Clever_Anjos
Employee
Employee

You can write your where condition right above your SQL

Table1:
Load
key as B
*;
Select

.. from TableA;

Table2:
Load
key as B
*

where not exists(key,B);
Select

.. from TableA;

Anonymous
Not applicable
Author

HI,

You should try this:

Table1:
Load
key as B,
*;
Select

.. from TableA;

Table2:
Load
key as B_temp,
*;
Select

.. from TableA;



CONCATENATE (Table1)

LOAD

*,

B_temp as B

RESIDENT Table2

Where not exists(B,B_temp);


DROP TABLE Table2;

DROP FIELD B_temp;

Not applicable
Author

Thank you everyone.

Once I have loaded my tables, I need to sum one fields group by other.

But :

Load xx, sum(A)

where not exists (key,key)

group by xx;

LOAD *

resident Table2;

doesn't work...

swuehl
MVP
MVP

What do you mean with 'doesnt't work', do you get a script error message or do you get results different from your expectation?

Could you post a small sample QVW that demonstrates your issue (you could load some mock up data using INLINE,  you don't need to use your real data)?

Not applicable
Author

The problem is when I do :

Load A, sum(XX)

group by A;

Table1:
Load
key as B
*;
Select

.. from TableA;

Table2:
Load
key as B
*

where not exists(key,B);
Select

.. from TableA;

The sum and group by applies only on Table1 (Before Table1 and Table2 are automatically concatenated) and so in te end I get 2 tables (with asynthetic table) .

How can I specify I want the sum and group b apply on both Table1 and Table2 (concatenanated). I have tried brackets but it doesn't work.

Thank you for your help

swuehl
MVP
MVP

Really not sure what you want to achieve here. You could try adding the GROUP BY and the SUM() also to the second table load, if all field names are identical, the table should get auto-concatenated. You can also use CONCATENATE LOAD prefix to force concatenation.

Besides this, I think your exists(key, B) is not correct, since B is not a field from your input records, right?