topic Re: Don't load data without associated records in QlikView

Don't load data without associated records

Mon, 10 Mar 2014 16:40:56 GMT

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 *;

Re: Don't load data without associated records

alexandros17 — Mon, 10 Mar 2014 16:43:14 GMT

Use instruction EXISTS ...

Re: Don't load data without associated records

Nicole-Smith — Mon, 10 Mar 2014 16:45:42 GMT

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 *;

Re: Don't load data without associated records

Mon, 10 Mar 2014 16:56:53 GMT

where Exists is the solution

Re: Don't load data without associated records

Mon, 10 Mar 2014 17:05:58 GMT

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;

Re: Don't load data without associated records

Nicole-Smith — Mon, 10 Mar 2014 17:14:59 GMT

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

Re: Don't load data without associated records

Mon, 10 Mar 2014 17:30:35 GMT

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

Re: Don't load data without associated records

Nicole-Smith — Mon, 10 Mar 2014 17:34:15 GMT

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).

Re: Don't load data without associated records

Mon, 10 Mar 2014 17:53:58 GMT

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

Re: Don't load data without associated records

Nicole-Smith — Mon, 10 Mar 2014 17:56:20 GMT

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.

Re: Don't load data without associated records

Mon, 10 Mar 2014 18:17:08 GMT

Thanks Nicole, the UNQUALIFY statement worked!