Exists on Subfield or Alias

[#1]

Hi everyone,

I am trying to use Exists to exclude results that exists in the Excelfile i am loading but doesn´t in the previously loaded SQL tables.

OrderID is the key. However it loads OrderID´s that has not previously been loaded.

I tried to use exsists in the first load to no avail. Happy for any input!

I.e Exists(OrderID) or Exists(Subfield([Master Reference], ., 1) AS OrderID) doesn´t work

 
MAINFILE:
REPLACE LOAD  
      SubField([Master Reference], ., 1) AS OrderID
   
FROM[Mainfile.xls]
;
FINALFILE:
NoConcatenate LOAD  
      OrderID 
Resident MAINFILE
Where Exists(OrderID)
;
DROP  Table MAINFILE
;  

[swuehl · Accepted solution]

Shouldn't your subfield delimiter / second parameter be '.' (enclosed in single quotes)?

Anyway, I think you should use the two parameter version of exists() function:

FINALFILE:
REPLACE LOAD 
      SubField([Master Reference], '.', 1) AS OrderID
  
FROM[Mainfile.xls]where Exists(OrderID, SubField([Master Reference],'.',1))
;


[#3]

Great, that works just like i hoped.I didn´t know about the two parameter version. Thanks!