topic Matching Dates in QlikView

Matching Dates

Tue, 22 Sep 2009 04:23:23 GMT

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

Matching Dates

Tue, 22 Sep 2009 11:46:21 GMT

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;

Matching Dates

Tue, 22 Sep 2009 14:55:45 GMT

Perfect! Much appreciated.

Matching Dates

Tue, 22 Sep 2009 18:36:53 GMT

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?

Matching Dates

blaise — Tue, 22 Sep 2009 19:02:29 GMT

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 🙂

Matching Dates

Tue, 22 Sep 2009 19:02:52 GMT

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!

Matching Dates

Tue, 22 Sep 2009 19:23:49 GMT

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

Matching Dates

Tue, 22 Sep 2009 19:34:53 GMT

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

Matching Dates

Tue, 22 Sep 2009 20:34:26 GMT

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;

Matching Dates

Tue, 22 Sep 2009 20:44:10 GMT

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

Matching Dates

Tue, 22 Sep 2009 21:13:35 GMT

But then doing this then the Date field within Schedule Doesnt Join

Thanks for your help with this!

Matching Dates

Tue, 22 Sep 2009 22:30:21 GMT

I wanted you to create the Calendar table like this:


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

This way, you will link with the first Table using Date, and Second Table using DateCreated

Matching Dates

Tue, 22 Sep 2009 22:48:46 GMT

Sorry I see, that Makes sense.

although when trying that I know get a script reload error, When stepping through it as it loads the calendar last it's just after this it now generates the "Execution of Script failed. reload old Data? Message.

Thanks again

Matching Dates

Tue, 22 Sep 2009 23:04:31 GMT

hmm..can't tell anything on that, unless you can post a sample qvw file.

Matching Dates

Tue, 22 Sep 2009 23:11:21 GMT

Thanks for all your help, will look to work my way back through it and post a scrambled file if i can't get to the bottom of it!

Cheers

Gez

Matching Dates

Tue, 22 Sep 2009 23:11:45 GMT

Thanks for all your help, will look to work my way back through it and post a scrambled file if i can't get to the bottom of it!

Cheers

Gez