Not exist condition

[YoussefKacem1]

Hello everyone , 

I have this script :

Table1:

LOAD   ID,  AuditType , Date ,  Store.Code , Score 

from Source.QVD  

This script show me the stores that got audited , their score and All sort of audit type (more than 7 types ) 

I want to know the UnauditedStores  
I tried this  script but without result  
Load Store.Code , 1 as FLAG 

Resident Table1 

where AuditType = 'Audit1'

and not exists (Store.Code)

Thank you in advance! 

[Kushal_Chawda]

@YoussefKacem1  try below. Idea is you first need to find the stores which are audited then on next load you can use not exist using that ID

Data:
LOAD   ID,  
      AuditType , 
      Date ,  
      if(AuditType='Audit1',Store.Code) as Store.Code.Audit,
      Store.Code , 
      Score 
from Source.QVD ;

Left Join(Data)
LOAD distinct Store.Code,
              1 as Flag
Resident Data
where not Exists(Store.Code.Audit,Store.Code);

drop field Store.Code.Audit;