0

We are using a Master DE that is a combination of two Objects and tying to filter from it to generate records to meet certain criteria. When we use the drag and drop filter from the Master DE, we don’t get the correct amount of records that are cross checked against the report in Financial Service Cloud.



When we run the same query we are using to combine our two objects that make up the Master DE and add the drag and drop filter logic to the query, we get the correct amount of records….Curious if something either sticks out in the query or filter logic to cause the difference in numbers…

SELECT 
        a.[BillingCity],
        a.[BillingCountry],
        a.[BillingPostalCode],
        a.[BillingState],
        a.[BillingStreet],
        a.[Business_Line_TS_Securities__c],
        a.[CreatedById],
        a.[FinServ__PrimaryContact__c],
        a.[FinServ__ReferredByContact__c],
        a.[FinServ__ReferredByContact__pc],
        a.[FinServ__ReferredByUser__c],
        a.[FinServ__ReferredByUser__pc],
        a.[FirstName],
        a.[ID] as Account_ID,
        a.[IsPersonAccount],
        a.[LastModifiedById],
        a.[LastName],
        a.[MasterRecordId],
        a.[OwnerId],
        a.[ParentId],
        a.[PersonContactId],
        a.[PersonEmail] as [fs.Owner_Email__c],
        a.[PersonHasOptedOutOfEmail],
        a.[PersonIndividualId],
        a.[RecordTypeId],
        a.[Name],
        a.[Type],
        a.[Asset_Classes_for_Accounts__pc],
        a.[Business_Line_TS_International__pc],
        a.[Business_Line_TS_Securities__pc],
        a.[Business_Line_YCT__pc],
        a.[Contact_Created_on__pc],
        a.[FinServ__ContactPreference__pc],
        a.[CreatedBy__pc],
        a.[CreatedDate],
        a.[FinServ__CreatedFromLead__pc],
        a.[Customer_ID__pc],
        a.[Domestic_or_International__pc],
        a.[DynamicsId__pc],
        a.[FinServ__IndividualId__pc],
        a.[FinServ__IndividualType__pc],
        a.[Industry__pc],
        a.[LastModifiedBy__pc],
        a.[No_More_Business__pc],
        a.[Region__pc],
        a.[Relationship_Manager__c],
        a.[Relationship_Manager__pc],
        a.[Securities_and_International_Opt_Out__pc],
        a.[TS_Customer_Segment__pc],
        a.[TS_Customer_Type__pc],
        a.[TS_Sec_Business_Line__pc],
        a.[VIP_Level__pc],
        a.[YCT_Courses__pc],
        a.[YCT_Customer_Segment__pc],
        a.[YCT_Opt_Out__pc],
        a.[Region_Id__pc],
        a.[TS_Customer_Type__c],
        fs.[Account_Closed_Date__c],
        fs.[Account_Fund_Date__c],
        fs.[Account_Type__c],
        fs.[Application__c],
        fs.[Application_Approval_Date__c],
        fs.[Asset_Class__c],
        fs.[Brokerage_Account__c],
        fs.[Created_By__c],
        fs.[CreatedBy__c],
        fs.[Crypto_Account_Enabled__c],
        fs.[EQ__c],
        fs.[FinServ__AssetRebalance__c],
        fs.[FinServ__Household__c],
        fs.[FinServ__JointOwner__c],
        fs.[FinServ__PrimaryOwner__c],
        fs.[Last_Modified_By__c],
        fs.[Linking_Rep_ID__c],
        fs.[Name] AS FinServ__Name,
        fs.[Opportunity__c],
        fs.[Id] AS FinServ__Id,
        fs.[Option_Trades__c],
        fs.[Options_Enabled__c],
        fs.[Options_Level__c],
        fs.[OwnerID] AS FinServ__OwnerId,
        fs.[Run_Rate_Status__c],
        fs.[RunRate_Status_Date__c],
        fs.[Sales_Rep__c],
        fs.[CreatedByID] AS FinServ__CreatedByID,
        fs.[CreatedDate] AS FinServ__CreatedDate,
        fs.[RecordTypeId] AS FinServ__RecordTypeId,
        fs.[Linking_RepID_Type__c],
        fs.[Platform__c],
        fs.[LastModifiedById] AS FinServ__LastModifiedById
FROM ENT.[Account_Salesforce_4] a
Left JOIN ENT.[FinServ__FinancialAccount__c_Salesforce_1] fs 
    ON a.[ID] = fs.[FinServ__PrimaryOwner__c]
    Where [Securities_and_International_Opt_Out__pc] = 'False'
    AND a.[PersonHasOptedOutOfEmail] = 'False'
    AND a.[No_More_Business__pc] = 'False'
    AND a.[IsPersonAccount] = 'True'
    AND a.[PersonContactId] IS NOT NULL
    AND TS_Customer_Type__pc = 'Retail'
    AND Asset_Classes_for_Accounts__pc = 'Equities Crypto'
    AND Account_Fund_Date__c >= '9/30/2020'
    AND Account_Fund_Date__c <='10/15/2020']

Filter Logic:enter image description here

3
  • 1
    Your SQL is doing a left join. However, your data filters is filtering based on only certain conditions. Data filters are normal 'and' clauses of SQL. They will not give you any records that are resulting from SQL left join. Nov 4, 2020 at 8:56
  • @SwatiMishra Thank you for the insight. Wouldn't the mater produce the same results though given the SQL is the same?
    – MB360
    Nov 4, 2020 at 12:51
  • yes, if in your SQL you use only the conditions used in the filter it would give the same result. Nov 4, 2020 at 13:02

1 Answer 1

0

It could be your dates in the where clause.

Try to cast the dates and use datediff in your sql where clause so it's not comparing strings, apply date diff for both comparisons. For example.

DATEDIFF(day, CAST (Account_Fund_Date__C AS DATE), CAST("2020-09-30" AS DATE)) <=0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .