Bizarre calendar behaviour

[#1]

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');

[alexandros17]

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)

[#3]

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

[MarcoWedel]

Please post sample application

Regards

Marco

[jonathandienst]

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.

[#6]

But it's one standard date field in Salesforce. The format doesn't vary

So why would it load some records and not others?

[#7]

attached

You won't be able to run it though, as I have stripped out the connection string

[#8]

It throws the error

Field not found - <Date#Task.ActivityDate>

FYI the format is YYYY-MM-DD

[jonathandienst · Accepted solution]

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.

[jonathandienst]

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