Hi All,
basically
i need to extract ContactID from Lead based on some conditions similarly ContactID from Contact table based on some conditions
after getting ContactID's from both tables , i need to count contactID.Contact not exist in contactid.lead
below i have script level solution but i need this in set expression
this is script level
LeadMQL:
LOAD
Datasource.Lead,
DateMQL.Lead,
QuarterName(DateMQL.Lead) as LeadMQL,
%ID.Lead ,
LeadSource.Lead,
Status.Lead,
%ID.Contact as ContactLead,
Source.Lead,
WOCRecord.Lead,
IsLead.Lead
FROM [lib://Downloads:Data/Lead.qvd]
(qvd) where WOCRecord.Lead = '0'
and Status.Lead <> 'LeanData Auto-Merge'
;
ContactMQL:
LOAD
%ID.Contact,
Email.Contact,
LeadSource.Contact,
DateMQL.Contact,
ContactStatus.Contact,
IsConverted.Contact,
QuarterName(DateMQL.Contact) as ContactMQL
FROM [lib://Downloads:Dat/Contact.qvd]
(qvd)
where not exists(ContactLead,%ID.Contact)
//and QuarterName(DateMQL.Contact) = QuarterName(AddMonths(Today(),0))
//and QuarterName(DateMQL.Contact) = QuarterName(AddMonths(Today(),-3))
and WoCRecord.Contact ='0'
;
i need this is setexpression
Thank you
Channa
