If(match( problem in script data load editor...

[Nolgath]

I have this code : 

Table1:
LOAD
id,
"date",
seller_name,
seller_address,
seller_post_code,
seller_city,
seller_contact,
seller_email,
seller_phone,
seller_country,
buyer_name,
buyer_address,
buyer_post_code,
buyer_city,
buyer_contact,
buyer_email,
buyer_phone,
buyer_country,
purchase_price,
purchase_currency,
sell_price,
sell_currency,
reg_nr,
make,
model,
vin_code,
made_year,
mileage,
"color",
seller_vat_number,
buyer_vat_number
FROM [lib://AttachedFiles/Buyers and Sellers Existing.csv]
(txt, utf8, embedded labels, delimiter is ';', msq);

Dealers:
LOAD DISTINCT
seller_name as dealer
FROM [lib://AttachedFiles/Buyers and Sellers Existing.csv](txt, utf8, embedded labels, delimiter is ';', msq);

CONCATENATE(Dealers)
LOAD DISTINCT
buyer_name as dealer
FROM [lib://AttachedFiles/Buyers and Sellers Existing.csv](txt, utf8, embedded labels, delimiter is ';', msq);

Table2:
LOAD
Country,
Dealer as dealer,
"Sold Cars",
"Online Cars",
"Year From",
"Year To",
"Km From",
"Km To",
"Price From",
"Price To",
"Base Currency",
StockTurn, 
IF(MATCH(Dealers.dealer, Table2.Dealer), 'exist', 'noexist') as MatchStatus

FROM [lib://AttachedFiles/Dealer export.xlsx]
(ooxml, embedded labels, table is Sheet1);

What I am trying to do with the if(match( is that if dealers fields from table Dealers/Table1 matches Table2 dealers then in new field MatchStatus i get 'Exist' or 'Does not Exist'.

But i keep getting this error no matter what i change:

The following error occurred:

Field 'Dealers.dealer' not found

The error occurred here:

Table2: LOAD Country, Dealer as dealer, "Sold Cars", "Online Cars", "Year From", "Year To", "Km From", "Km To", "Price From", "Price To", "Base Currency", StockTurn, IF(MATCH(Dealers.dealer, Table2.Dealer), 'exist', 'noexist') FROM [lib://AttachedFiles/Dealer export.xlsx] (ooxml, embedded labels, table is ***)

Any help here? please..

[Lemac]

You are making a whole new table, it is not possible to reference to previously loaded tables, since there is no connection

I guess you should do this with a left join?

left join ( Dealers)
Load
Dealer as dealer,
'Exist' as MatchStatus

From ... etc..

[ogster1974 · Accepted solution]

Create a mapping table of Dealers from table one

Mapping

DealerMap:

Load

        dealer,

        'Exisits'

Resident Dealers

;

then applymap('DealersMap', dealer,'NoExists') as MatchStatus instend of if statement.