topic Re: Where not exist in QlikView

Where not exist

Mon, 22 Feb 2016 13:49:35 GMT

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

Re: Where not exist

luciancotea — Mon, 22 Feb 2016 13:56:59 GMT

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;

Re: Where not exist

swuehl — Mon, 22 Feb 2016 13:58:59 GMT

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.

Re: Where not exist

Clever_Anjos — Mon, 22 Feb 2016 14:00:07 GMT

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;

Re: Where not exist

Anonymous — Mon, 22 Feb 2016 14:03:27 GMT

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;

Re: Where not exist

Tue, 23 Feb 2016 17:11:51 GMT

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...

Re: Where not exist

swuehl — Tue, 23 Feb 2016 17:17:52 GMT

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)?

Re: Where not exist

Wed, 24 Feb 2016 10:59:24 GMT

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

Re: Where not exist

swuehl — Wed, 24 Feb 2016 17:21:07 GMT

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?