topic Re: exists in QlikView

exists

raadwiptec — Wed, 25 Nov 2020 16:16:04 GMT

I have 2 tables - Both of them as same structure

Table1

========

product id

price

date

...

few colums

Table2

========

product id

price

date

...

few colums

here if the same product id exists in table2. I would like to exclude it

Re: exists

jyothish8807 — Fri, 21 Oct 2016 07:21:58 GMT

Hi Raad,

Try like this:

Table 1:

Load

product id

price

date

from Sheet1;

Table2:

Load

product id

price

date

from Sheet1

where not exists (product id);

Regards

Re: exists

jonathandienst — Fri, 21 Oct 2016 07:27:58 GMT

Create a temporary field for the ids loaded from table 2 and use a not exists to prevent those from being loaded from table1:

Table2:

LOAD *,

[product id] as productid_loaded_in_table2

FROM Table2;

Concatenate(Table2)

LOAD * from Table1

WHERE Not(Exists(productid_loaded_in_table2, [product id]));

DROP Field productid_loaded_in_table2; // drop temp field

Adjust the load statements to your specific requirements. This logic will allow one or many records with the same id to come from table1 as long as the id does not exist in table2.

Re: exists

raadwiptec — Fri, 21 Oct 2016 10:51:09 GMT

hi jonathan,

can you tell me why these 2 fields are used in not exists. We should only input the field of second table right?

WHERE Not(Exists(productid_loaded_in_table2, [product id]));

Re: exists

maxgro — Fri, 21 Oct 2016 12:16:58 GMT

WHERE Not(Exists(productid_loaded_in_table2, [product id]));

the productid_loaded_in_table2

is the field you loaded from Table2 (first statement)

the [product id]

is the field you're loading from Table1 (second statement)

Re: exists

Peter_Cammaert — Fri, 21 Oct 2016 12:22:51 GMT

Jonathan's last sentence answers your question. It comes down to this: from which table do you want to allow multiple records per product? Jonathan's example allows duplicates in Table 2 and duplicates from Table 1 as long as the products from Table 1 aren't already present in Table 2.

If you would like to load 1 row per product from Table 1 as well as one row per product from Table 2 on condition that they don't already exist in Table 1, then use a single product_id and a not exists() on each load. That will get you a maximum of 1 record per product id from both tables thrown together.

YMMV

Peter

Re: exists

raadwiptec — Fri, 21 Oct 2016 12:45:52 GMT

hi peter,

as per jonanthan iam still getting values from both the sources table 1 and table 2 but if i use the below

Table1:

LOAD *,

[product id] ,

...

FROM Table1;

Concatenate(Table2)

LOAD * from Table1

WHERE Not Exists([product id]));

Iam getting the correct results. is their anything wrong here?

Re: exists

Peter_Cammaert — Fri, 21 Oct 2016 12:52:13 GMT

No, nothing is wrong with this version. If it works, then it's correct.

BTW this is the solution that Jyothish suggested at the start of this thread.

Re: exists

Mon, 24 Oct 2016 05:46:39 GMT

No Nothing wrong in that,

Where Exists will fetch the data from second key field table based on first key field table like matched records.

Where Not exists fetch the data from second key field table based on first key field table like unmatched records.