topic Re: Bizarre calendar behaviour in QlikView

Bizarre calendar behaviour

Thu, 10 Sep 2015 08:09:40 GMT

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

Re: Bizarre calendar behaviour

alexandros17 — Thu, 10 Sep 2015 08:13:58 GMT

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)

Re: Bizarre calendar behaviour

Thu, 10 Sep 2015 08:18:46 GMT

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

MarcoWedel — Thu, 10 Sep 2015 08:26:24 GMT

Please post sample application

Regards

Marco

Re: Bizarre calendar behaviour

jonathandienst — Thu, 10 Sep 2015 08:45:40 GMT

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.

Re: Bizarre calendar behaviour

Thu, 10 Sep 2015 08:48:30 GMT

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

So why would it load some records and not others?

Re: Bizarre calendar behaviour

Thu, 10 Sep 2015 08:51:21 GMT

attached

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

Re: Bizarre calendar behaviour

Thu, 10 Sep 2015 08:59:59 GMT

It throws the error

Field not found - <Date#Task.ActivityDate>

FYI the format is YYYY-MM-DD

Re: Bizarre calendar behaviour

jonathandienst — Thu, 10 Sep 2015 09:01:16 GMT

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.

Re: Bizarre calendar behaviour

jonathandienst — Thu, 10 Sep 2015 09:02:30 GMT

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

Re: Bizarre calendar behaviour

Thu, 10 Sep 2015 09:23:53 GMT

Thanks Jonathan. You're a legend! A couple of questions

1) How did you spot the time stamp of 23:00:00?

2) I have had this integration with Qlikview and Salesforce for a couple of years and this has never been an issue. Do you think it could be the new Salesforce connector?

Re: Bizarre calendar behaviour

qlikmsg4u — Thu, 10 Sep 2015 09:31:12 GMT

Convert activity date to Timestamp you will find the time values

Re: Bizarre calendar behaviour

jonathandienst — Thu, 10 Sep 2015 10:36:59 GMT

Alex Hamilton McLeod wrote:

1) How did you spot the time stamp of 23:00:00?

2) I have had this integration with Qlikview and Salesforce for a couple of years and this has never been an issue. Do you think it could be the new Salesforce connector?

1) Following a hunch, I displayed the dates in timestamp format

2) I suspect that the business process has changed and taht is causing Salesforce to add a time value - but that is just one possible cause...

Re: Bizarre calendar behaviour

MarcoWedel — Thu, 10 Sep 2015 10:45:56 GMT

Hi,

maybe it's some daylight saving time conversion issue:

hope this helps

regards

Marco