Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Bizarre calendar behaviour

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!

interactions.jpg

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Bizarre calendar behaviour

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
13 Replies

Re: Bizarre calendar behaviour

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)

Not applicable

Re: Bizarre calendar behaviour

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

Re: Bizarre calendar behaviour

Please post sample application

Regards

Marco

MVP
MVP

Re: Bizarre calendar behaviour

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Bizarre calendar behaviour

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

So why would it load some records and not others?

Not applicable

Re: Bizarre calendar behaviour

attached

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

Not applicable

Re: Bizarre calendar behaviour

It throws the error

Field not found - <Date#Task.ActivityDate>

FYI the format is YYYY-MM-DD

MVP
MVP

Re: Bizarre calendar behaviour

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MVP
MVP

Re: Bizarre calendar behaviour

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Community Browser