topic Exists on Subfield or Alias in QlikView

Exists on Subfield or Alias

Fri, 13 Jan 2012 14:07:21 GMT

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
;

Exists on Subfield or Alias

swuehl — Fri, 13 Jan 2012 14:21:37 GMT

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)) ;

Exists on Subfield or Alias

Fri, 13 Jan 2012 14:36:15 GMT

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