HOW TO RETRIEVE UNIQUE RECORDS IN A LOAD STATEMENT

[#1]

Greetings again!

I am totally stuck on something very very basic... I cannot retrieve unique records from a LOAD statement... I have searched for a solution as much as I could over the forum and the web, but it turned out fruitless...

Here's the thing:
I have two tables, with these relevant fields:

Table1:
Costumer_Shipper Teus
--------------------------------------------
customer1 50
customer2 100
customer3 20
... ...

Table2:
Costumer_Shipper Partner_Name Partner_Code
-----------------------------------------------------------------------------------
customer1 namex BR001
customer2 namey BR002
customer3 namez BR003
... ... ...

PS1: on table1, natively, none of the fields guarantee atomicity and integrity of data records.
PS2: the same is applied to table2.

I must crosscheck these two tables (from different datasources) to retrieve the records that are on table1, but that are not referenced at table2 (through the common field Costumer_Shipper) ...

SO, all integrity must be created during the LOAD statement, and SO we reach my torment... how can I do this at QV?

I tried to perform all kinds of JOINS, [NO]CONCATENATE, complex WHERE clauses, and I still have no trustable results (impossible to achieve with no integrity rules).

I heard about the AUTONUMBER feature, but still a bit confusing so far.

Could anyone point me in the right direction? QV is a fantastic tool, but too diferente from standard SQL DBMSs...

Regards, and thank you!!!

[#2]

Adding the code I wrote:

Dicionario_VS_Market:

LOAD

Customer_Shipper,

Teus

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

LEFT JOIN LOAD

Shipper AS Customer_Shipper,

[Partner Code],

[Partner Name]

FROM [..\..\Datos Externos\CSAV_BRASIL\dicionario mercado-csav\dicionario.xls] (biff, embedded labels, table is Sheet1$);

Dicionario_VS_Market_CROSSCHECKED:

LOAD DISTINCT

SUM(Teus) AS SUMTEUS,

AUTONUMBER (Customer_Shipper & [Partner Code], '001') AS KEY

RESIDENT Dicionario_VS_Market

WHERE (

[Partner Code] <> PREVIOUS([Partner Code])

AND Customer_Shipper <> PREVIOUS(Customer_Shipper)

AND NOT MIXMATCH(Customer_Shipper, 'not found')

);

DROP TABLE Dicionario_VS_Market;

Standing by!

[#3]

Guys???

=S

[saxjonas]

I haven't run any tests and this might be a long shot but have you tried the exists() function in a where clause?

Table2:
LOAD:
....
FROM ...
WHERE not exists(CustID,CustID);

[#5]

Jonas, thank you for answering!

I tried the NO EXISTS at first, but it did not worked... when the same field is compared in that clause, no matches are found...

I tried to use the CONCATENATE to bring the common field between the tables as distinct fields, and then compare then with the NO EXISTS, but it did not worked as well...]

Thats the reason I have posted such a long (and sad) story... I'm new to QlikView (one month), and I don't want to screw it up so soon...

Standing by, and thank you very much one more time!

[#6]

hey,

if i understood corectly you want to find all the customers from table 1 which does not exist in table 2?

If so the answer is simple: 😛

Load distinct * resident Dicionario_VS_Market where isnull([Partener Code])

[#7]

Greetings Ionut,

It would perfectly fit the case if partnercode was a UNIQUE field... the datasources have no integrity control, and most of what should be NULL fields are BLANK fields.

The challenge here is to establish integrity in the LOAD process...

Anyway, I'm giving it a shot.

Thank you very much for answering!