topic Re: Getting counts on dimensions from 2 tables in QlikView

Getting counts on dimensions from 2 tables

Tue, 14 Aug 2012 16:14:17 GMT

I am trying to get counts by a dimension from 2 tables:

Table: Leads
Lead_Created_Date	LeadId
DD/MM/YYYY	L1
DD/MM/YYYY	L2
DD/MM/YYYY	L3
DD/MM/YYYY	L4
DD/MM/YYYY	L5

Table: Contacts
Contact_Created_Date	ContactId
DD/MM/YYYY	C1
DD/MM/YYYY	C2
DD/MM/YYYY	C3
DD/MM/YYYY	C4
DD/MM/YYYY	C5

I want to a chart to display total leads + total contacts by created date. I have tried combining the created_date fields by using a calculated dimension of:

=if(isnull(monthname(ContactCreatedDate)), monthname(LeadCreatedDate), monthname(ContactCreatedDate))

And an expression of:

=Count (distinct ContactId) + Count (DISTINCT LeadId)

But when I drill down into the report it only gives me counts of where both a LeadId and a ContactId exists (converted leads). Any ideas on how I can combine these dimensions from 2 tables would be greatly appreciated.

Thanks

Jon

Re: Getting counts on dimensions from 2 tables

Wed, 15 Aug 2012 11:40:08 GMT

I resolved this by concatenating the lead and contact tables in the load script. I added an addtional field of [record type] to this combined table which I populated with either 'Lead' or 'Contact' so I am able to report on the breakdown if needed.

There was an issue linking this to the campaign member table from Salesforce as it created a synthetic key on LeadId and ContactId. To get around this I created a field called LeadContactId using the expression

if(len(ContactId)=0, LeadId,ContactId) as LeadContactId

And put my LeadId and ContactId into a field of the same name in my concatenated table.

This solution works for us as all of our shared lead and contact fields are standardised in the same way. It also makes it much easier to report campaigns where they can be a combination of leads and contacts.

Just thought I'd share this solution in case anyone else encounters a similar problem.

Thanks

Jon