If values exist in other fields

[Nolgath]

Hi, i have this issue I've been trying to solve:

Script:

sales:
LOAD
buyervat,
sellervat,
name
FROM [lib://AttachedFiles/salescustomer.xlsx]
(ooxml, embedded labels, table is Sales);

customers:
LOAD
VAT,
Id
FROM [lib://AttachedFiles/salescustomer.xlsx]
(ooxml, embedded labels, table is Customers);

I need to create a new field that will flag things for me:

if VAT exists in buyervat and sellervat then i get "Buyer/Seller".
if VAT exists only in one of them then i get "Buyer" or "Seller".
if VAT does not exist in either i get "-".

I have tried if(exists()) but not success I get wrong or missing matches..

Data used is attached, if have then the original data that i way bigger but i am trying to do with a smaller db first to understand this concept

[steeefan · Accepted solution]

One possible solution with the two tables already created:

LEFT JOIN (sales) LOAD
  VAT as buyervat,
  1 AS buyervatFlag
RESIDENT
  customers;

LEFT JOIN (sales) LOAD
  VAT as sellervat,
  1 AS sellervatFlag
RESIDENT
  customers;

sales2:
LOAD
  buyervat,
  sellervat,
  name,
  If(buyervatFlag and sellervatFlag, "Buyer/Seller",
    If(buyervatFlag and Not(sellerVatFlag), "Buyer",
      If(sellerVatFlag and Not(buyervatFlag), "Seller",
        Null()
      )
    )
  ) AS BuyerSeller
RESIDENT
  sales;

DROP TABLE sales;

[Ahidhar · Accepted solution]

try this

sales:
LOAD
buyervat,
sellervat,
name
FROM [lib://DataFiles/salescustomer.xlsx]
(ooxml, embedded labels, table is Sales);

map1:
mapping Load Distinct
buyervat as VAT,
1 as Flag
resident sales;

map2:
mapping load Distinct
sellervat as VAT,
1 as Flag1
resident sales;
customers:
LOAD
VAT,
Id,
if(Applymap('map1',VAT)=1 and Applymap('map2',VAT)=1,'Buyer/Seller',
if(Applymap('map1',VAT)=1,'Buyer',
if(Applymap('map2',VAT)=1,'Seller',null()))) as Flag
FROM [lib://DataFiles/salescustomer.xlsx]
(ooxml, embedded labels, table is Customers);