Don't load data without associated records

[#1]

I have three tables I'm loading. Activities, Contacts and Accounts (related as per below)

How do I change my load script for Accounts, to only include records which have associated activities?

QUALIFY *;

UNQUALIFY Activity.AccountId;

UNQUALIFY AccountIdforTag;

Accounts:

LOAD Id AS Activity.AccountId,

    Id AS AccountIdforTag,

    IsDeleted,

    Name,

    Country__c;

SQL SELECT *

FROM Account;

UNQUALIFY *;

Similarly how would I amend mt load script for Contacts, to only include contacts with related activities?

QUALIFY *;

UNQUALIFY Activity.ConatctRecordTypeId;

UNQUALIFY Activity.AccountId;

UNQUALIFY Activity.ContactId;

UNQUALIFY Activity.Department_Role__c;

UNQUALIFY Activity.Business__c;

Contacts:

LOAD Id as Activity.ContactId,

    AccountId AS Activity.AccountId,

    Name,

    RecordTypeId AS Activity.ConatctRecordTypeId,

    Department_Role__c AS Activity.Department_Role__c,

    Inactive_Contact__c,

    Business__c AS Activity.Business__c;

SQL SELECT *

FROM Contact;

UNQUALIFY *;

[alexandros17]

Use instruction EXISTS ...

[Nicole-Smith · Accepted solution]

Load your Activities table first.

Then load Contacts, then load Accounts using where clauses:

QUALIFY *;

UNQUALIFY Activity.ConatctRecordTypeId;

UNQUALIFY Activity.AccountId;

UNQUALIFY Activity.ContactId;

UNQUALIFY Activity.Department_Role__c;

UNQUALIFY Activity.Business__c;

Contacts:

LOAD Id as Activity.ContactId,

    AccountId AS Activity.AccountId,

    Name,

    RecordTypeId AS Activity.ConatctRecordTypeId,

    Department_Role__c AS Activity.Department_Role__c,

    Inactive_Contact__c,

    Business__c AS Activity.Business__c

WHERE exists(Activity.ContactId,Id);

SQL SELECT *

FROM Contact;

UNQUALIFY *;

QUALIFY *;

UNQUALIFY Activity.AccountId;

UNQUALIFY AccountIdforTag;

Accounts:

LOAD Id AS Activity.AccountId,

    Id AS AccountIdforTag,

    IsDeleted,

    Name,

    Country__c

WHERE exists(Activity.AccountId,Id);

SQL SELECT *

FROM Account;

UNQUALIFY *;

[#4]

where Exists is the solution

[#5]

Thanks Nicole. This worked great, EXCEPT the two tables 'roles' and 'Business' (as per the screenshot above) don't seem to have loaded correctly now. That change have affected them.

They are semi-colon separate values, so my load script for the table was

Businesses:

LOAD Activity.ContactId, TRIM(SUBFIELD(Activity.Business__c, ';')) AS Activity.Business

RESIDENT Contacts;

[Nicole-Smith]

Saying it isn't "loaded correctly now" doesn't tell me what it was doing before vs. what it's doing wrong now...

[#7]

Apologies. The businesses table is now a data island and no longer linked to the contact table. Same for the Roles table

[Nicole-Smith]

Looks like you're using different code for the Roles and Businesses tables than what was in your original post.  In your original post the field names in those tables in the screenshot are Activity.____, but in this new screenshot they are Roles.___ and Businesses.____.  That shouldn't have anything to do with the code that I gave you with the where exists clause.  You must have changed something else (I'm guessing with a QUALIFY).

[#9]

Strange. I definitely didn't change anything with teh script. It's something to do with the load order I think, as perviously the business and roles were loaded after the contacts but before Activities, and now they're loaded after Contacts and Activities

[Nicole-Smith]

Make sure you have UNQUALIFY *; before the business and roles tables.

Or just leave them between the Contacts and Accounts tables, as that shouldn't hurt.