Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
@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;