Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the following chart below, which is behaving oddly since i upgraded the Salesforce connector and made some minor changes.
EVERY record has a date associated with it, yet there is NULL column appearing at the end. What's even more strange is that only the months Nov through to Feb are missing!
My calendar script is as follows
ActivityCalendar:
LOAD
ActivityDate,
year(ActivityDate) as [Activity Year],
month(ActivityDate) as [Activity Month],
monthname(ActivityDate) as [Activity MonthYear],
if(len(Week(ActivityDate))=2,Year(ActivityDate) & Week(ActivityDate),
Year(ActivityDate) & '0' & Week(ActivityDate)) as [Activity YEARWEEK],
weekstart(ActivityDate) as [Activity Week],
week(ActivityDate) as [ActivityWeekNumber],
day(ActivityDate) as [Activity Day],
if(if(len(Week(Today(1)))=2,Year(Today(1)) & Week(Today(1)),Year(Today(1)) & '0' & Week(Today(1)))-1=if(len(Week(ActivityDate))=2,Year(ActivityDate) & Week(ActivityDate),
Year(ActivityDate) & '0' & Week(ActivityDate)),1,0) as ActivityIsCurrentWeek,
if(if(len(Week(Today(1)))=2,Year(Today(1)) & Week(Today(1)),Year(Today(1)) & '0' & Week(Today(1)))-2=if(len(Week(ActivityDate))=2,Year(ActivityDate) & Week(ActivityDate),
Year(ActivityDate) & '0' & Week(ActivityDate)),1,0) as ActivityIsPriorWeek
;
// Generate range of dates between min and max.
LOAD
date(DateMin + IterNo()) as ActivityDate // Link Field
WHILE DateMin + IterNo() <= DateMax
;
// Find min and max of date field values.
LOAD
min(datefield)-1 as DateMin
,max(datefield) as DateMax
;
// Load date field values.
LOAD
FieldValue('ActivityDate', RecNo()) as datefield
AutoGenerate FieldValueCount('ActivityDate');
The "missing" dates have a time portion (23:00:00), but your calendar does not, so they don't align. Use this:
OAD Task.Id AS ActivityId,
Task.Subject,
Date(Floor(Task.ActivityDate)) as ActivityDate,
This will strip out the time format and cause the dates to align properly with the calendar.
The problem is that the expression has no values for Nov-Feb and there are situations where expression is not related to a date (null values)
But in the database there are values for those months. Why would my script not pick them up
QUALIFY *;
UNQUALIFY Task.OwnerId;
UNQUALIFY ActivityId;
UNQUALIFY ActivityDate;
Activities:
LOAD Task.Id AS ActivityId,
Task.Subject,
//Task.ActivityDate AS ActivityDate,
Task.ActivityDate as ActivityDate,
Task.OwnerId,
Task.Description,
Task.Products_discussed__c,
Task.eCommerce_comments__c,
Task.Publish_Activity__c,
IF(LEN(Task.Type)=0,'Not stated in Salesforce', Task.Type) AS Type,
Task.number_of_individual_activities__c,
Task.Activity_type__c
WHERE EXISTS (Task.OwnerId,Task.OwnerId);
SELECT
Id,
Subject,
ActivityDate,
OwnerId,
Description,
Products_discussed__c,
eCommerce_comments__c,
Publish_Activity__c,
Type,
number_of_individual_activities__c,
Activity_type__c
FROM Task
WHERE Type NOT IN ('Email','Minor communication') AND Subject != NULL;
UNQUALIFY *;
//Many-to-many mapping relationship between Contacts and Activties
QUALIFY *;
UNQUALIFY ActivityId;
UNQUALIFY Activity.ContactId;
ActivityContactMapping:
LOAD TaskRelation.Id AS MappingId,
TaskRelation.RelationId AS Activity.ContactId,
TaskRelation.TaskId AS ActivityId
WHERE EXISTS(ActivityId,TaskRelation.TaskId);
SQL SELECT
Id,
RelationId,
TaskId
FROM TaskRelation;
UNQUALIFY *;
Please post sample application
Regards
Marco
I think the problem could be due to QV not recognising some of the dates. You may need to explicitly specify the date format in a Date#() type date interpretation. I don't know what date format you Salesforce connector is returning, but by way of example:
LOAD Task.Id AS ActivityId,
Task.Subject,
Date(Date#Task.ActivityDate, 'DD-MM-YYYY')) as ActivityDate,
Change the format string to the actual format produced by Salesforce.
But it's one standard date field in Salesforce. The format doesn't vary
So why would it load some records and not others?
attached
You won't be able to run it though, as I have stripped out the connection string
It throws the error
Field not found - <Date#Task.ActivityDate>
FYI the format is YYYY-MM-DD
The "missing" dates have a time portion (23:00:00), but your calendar does not, so they don't align. Use this:
OAD Task.Id AS ActivityId,
Task.Subject,
Date(Floor(Task.ActivityDate)) as ActivityDate,
This will strip out the time format and cause the dates to align properly with the calendar.
Alex Hamilton McLeod wrote:
It throws the error
Field not found - <Date#Task.ActivityDate>
Missing ( - my bad. Anyway this was not your problem, it was the time portion....