JOIN or KEEP: Which one brings the right answer?

[#1]

Greetings everyone!

I'm stuck on something I just can't stand anymore! LoL!

I have two tables:
-------------
cust1
cust2
cust3
cust4
.........

table2:
-------------
cust1
cust2
cust8
cust5
..........

with one common field (a string field).
I must link'em together (JOIN or KEEP), and compare them to bring the records from table1 that are not referenced in table2... sounds easy, but...

...THE PROBLEM IS: the common field I'll use to link them is the same I must use to compare them, and this is causing a lot of trouble...

Could someone help me out, please???

Thanx!!!

[pover]

Jimmy,

Can you help clarify what you are looking for by adding some more sample data and the resulting table you want?

I assume you want just one customer field that gives the data from both tables even if the don't exist in both tables?

Thanks.

[#3]

Greetings Karl, thx for answering!

Here is code so far:
Customer_Shipper,
Teus

Dicionario_VS_Market:

LOAD

FROM

[..\..\Qvd\CSAV_BRASIL\MercadoECSA.qvd](qvd);

LEFT

KEEP LOAD
Shipper as Customer_Shipper
FROM [..\..\Datos Externos\CSAV_BRASIL\dicionario mercado-csav\dicionario.xls] (biff, embedded labels, table is Sheet1$)
WHERE NOT EXISTS(Customer_Shipper,Shipper);

Dicionario_VS_Market_CROSSCHECKED:

LOAD

DISTINCT
Customer_Shipper,
SUM(Teus) as SOMATEUS
RESIDENT Dicionario_VS_Market
GROUP BY Customer_Shipper;

DROP

TABLES

Dicionario_VS_Market;

If you need any other kind of information, please let me know!

Thanx again!!

[#4]

Sorry for the messed up data... here is the code:

Dicionario_VS_Market:

//Table1

LOAD
Customer_Shipper,
Teus
FROM [..\..\Qvd\CSAV_BRASIL\MercadoECSA.qvd](qvd);

//Table2

LEFT

KEEP LOAD
Shipper as Customer_Shipper
FROM [..\..\Datos Externos\CSAV_BRASIL\dicionario mercado-csav\dicionario.xls] (biff, embedded labels, table is Sheet1$)

//Condition to exclude duplicate records (not sure if its right...

WHERE NOT EXISTS(Customer_Shipper,Shipper);

//Bringing the merged dataLOAD DISTINCT
Customer_Shipper,
SUM(Teus) as SOMATEUS
RESIDENT Dicionario_VS_Market
GROUP BY Customer_Shipper;

Dicionario_VS_Market_CROSSCHECKED:

DROP TABLES Dicionario_VS_Market;

[Oleg_Troyansky · Accepted solution]

Thiago,

it's quite difficult to understand what you are trying to accomplish, but here are some basics:

1. LEFT KEEP prefix acts as a filter - it will only load those rows that already have the same values in the identical fields. For example:

Tab1:

load Field1, ...

Tab2:

left keep (Tab1) load Field1, ...

In this example, only those rows from Tab2 will be loaded, when Field1 value already exists in Tab1.

2. the combination of LEFT KEEP and WHERE NOT EXISTS() should produce an empty set, because the two conditions are mutually exclusive.

3. If you need to compare between the two tables, you can load the same key twice, - once by the "common" name, and second time with a different name. The second instance of the key is not a key field anymore, and you can compare between the two and count them separately. For example:

Tab1:

load

Customer_Shipper,

Customer_Shipper as Customer_Shipper_Tab1,

...

Tab2:

load

Customer_Shipper,

Customer_Shipper as Customer_Shipper_Tab2,

...

[#6]

Greetings Oleg!

Sorry for the porr explanation, but you back2basics have truly helped me understand on more difference between QV language and standard SQL.

Thank you very much for sharing your wisdom!

[#7]

Hi Thiago Ribeiro,

Both will give the same answer. the one & only difference between JOIN and KEEP is table view.

If u r using join means in table structure merged and show it to u one table.

if u r using keep means the answer wil b same but its shows the table separtely.

hope it help u

[#8]

Hi Thiago Ribeiro,

Both will give the same answer. the one & only difference between JOIN and KEEP is table view.

If u r using join means in table structure merged and show it to u one table.

if u r using keep means the answer wil b same but its shows the table separtely.

hope it help u

[#9]

Greetings Ms. Sri!

Thank you very much for your wise words.

I'll be on a QV class training next week (3-day straight), and I hope to bother you not with such seely questions anymore!

Thanx again 😃