exists

[raadwiptec]

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

[jyothish8807]

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

KC

[jonathandienst]

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.

[raadwiptec]

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]));

[maxgro]

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)

[Peter_Cammaert]

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

[raadwiptec]

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?

[Peter_Cammaert]

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.

[#9]

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.