Matching Dates

[#1]

Hi There,

Hoping someone can help point me in the right direction.

I'm loading two tables from QVD files.

LOAD [UserID],
CompanyID,
SiteID,
interval(EndTime-StartTime,'mm') as ScheduleDuration,
//ServiceID,
StartTime,
EndTime,
ScheduleTypeID
FROM
[Schedule.qvd]
(qvd);

LOAD WorkID,
CallID,
UserID,
CompanyID,
ServiceID,
EmployeeID,
// [UserID],
CallStateTypesID,
Priority,
WorkDate,
DateCreated,
DateUpdated,

FROM
[Work.qvd]
(qvd);

At the momment i have two separate Calenders based on the DateUpdated Field and the StartTime field. I would like to have one Calender in the Application. How can i go around joining the dates? Ive looked at the intervalmatch command but cant quite get it. Any help would be much appreciated. I'm using Qlik Version 9.0

Many Thanks

Gez

[#2 · Accepted solution]

Create two common date fields in two tables by renaming the required fields. Then create a calendar table and link it to the tables using the new field.

Table1:

LOAD [UserID],

CompanyID,

SiteID,

interval(EndTime-StartTime,'mm') as ScheduleDuration,

//ServiceID,

StartTime,

StartTime as Date,

EndTime,

ScheduleTypeID

FROM

[Schedule.qvd]

(qvd);

Table2:

LOAD WorkID,

CallID,

UserID,

CompanyID,

ServiceID,

EmployeeID,

// [UserID],

CallStateTypesID,

Priority,

WorkDate,

DateCreated,

DateUpdated,

DateUpdated as Date

FROM

[Work.qvd]

(qvd);

Calendar:

LOAD

date(makedate(2009)-1 + recno(), 'YYYY-MM-DD') as Date

AUTOGENERATE 365;

[#3]

Perfect! Much appreciated.

[#4]

Although the Dates are now linked, when selecting a date from the Calendar it only returns the correct information for the Schedule Table and not the Work Table. If I break the join and test the date fields separately the information returned is correct.

I've looked through the date format of both tables and it is returned in the same DD/MM/YY, Any other ideas?

[blaise]

Please send a screenshoot of the table viewer (ctrl+t). By renaming the two datefields to "date" you will add another common field from your two qvds (among f.ex. CompanyID...)

So ill guess your datamodel is quite ugly to be honest 🙂

[#6]

I can see that you have another common field in the two tables, CompanyID. This will create a circular reference among the 3 tables, and will result in wrong interpretations. So, you got to break the circular reference!

[#7]

Data Model attached, Sorry if this seems quite Basic, although i do need the Schedule and work Table to Join?

[#8]

Oops!! it was the synthetic key, the problem maker!! I think you can do one thing:

Keep the Date in Table1

Remove the Date in Table2 (work table).

In Calendar Table, add one more field

ie, date as WorkDate.

This will do i think

[#9]

Thanks for the reply,

Adding in Date as DateCreated causes an error as it can't find the date field, so it looks as below.

LOAD
date(makedate(2009)-1 + recno(), 'DD-MM-YYYY') as Date,
Date As DateCreated
AUTOGENERATE 365;

[#10]

Make it date(makedate(2009)-1 + recno(), 'DD-MM-YYYY') as DateCreated