topic Intervalmatch issue in QlikView

Intervalmatch issue

Fri, 28 Oct 2011 08:46:56 GMT

Hi, I am having a problem with an interval match. My main problem is that I don´t really understand how Intervalmatch works. The result from the script below seems to be correct, but what I am struggling with is the metadata and the names of the tables.

When I look at the table viewer the result is one "Calendar" Table, with Date as KEY connected to two tables, "DateTemp"-table and one "FlowCaseActivityTemp-1"-table. The "DateTemp"-table only contains Date.

In other words QlikView automatically names the link-table. I would like the result to be Only one linktable connected to the "Calendar"-table and and want to be able to name that table.

I don´t know if this makes any sence?

It would be great if someone could look at the script below and give some comments regarding how it could be improved.

BR//Hans

DateTemp:

LOAD
Date
Resident Calendar;

LOAD
StartDate,
EndDate,
FlowCaseActivityID
RESIDENT FlowCaseActivityTemp;

LEFT JOIN

INTERVALMATCH (Date)
LOAD
StartDate,
EndDate
RESIDENT FlowCaseActivityTemp;

Intervalmatch issue

Fri, 28 Oct 2011 09:26:58 GMT

hi,

try something like:

DateTemp:

LOAD

Date

Resident Calendar;

LINKTABLE: // <------------------- fix the name

LOAD

StartDate,

EndDate,

FlowCaseActivityID

RESIDENT FlowCaseActivityTemp;

LEFT JOIN

IntervalMatch(Date)

LOAD

StartDate,

EndDate

RESIDENT FlowCaseActivityTemp;

drop table DateTemp; // <------------ delete unused table

Intervalmatch issue

Fri, 28 Oct 2011 11:48:50 GMT

Thanks for your answer. Actually I allready tried that without success.
But when I change place of Date and LinkTable it worked:

ActivityLinkTable:

LOAD
StartDate,
EndDate,
FlowCaseActivityID
RESIDENT FlowCaseActivityTemp;

DateTemp:
LOAD
Date
Resident Calendar;

LEFT JOIN (ActivityLinkTable)
INTERVALMATCH (Date)
LOAD
StartDate,
EndDate
RESIDENT ActivityLinkTable;

Drop

Table DateTemp;

Intervalmatch issue

avastani — Fri, 28 Oct 2011 12:03:53 GMT

try this,

ActivityLinkTable:

LOAD
StartDate,
EndDate,
FlowCaseActivityID
RESIDENT FlowCaseActivityTemp;

DateTemp:
LOAD
Date
Resident Calendar;

LEFT JOIN INTERVALMATCH (Date)
LOAD
StartDate,
EndDate
RESIDENT ActivityLinkTable;

LEFT JOIN (DateTemp)
LOAD * RESIDENT ActivityLinkTable;

Drop Table DateTemp;

Intervalmatch issue

Mon, 31 Oct 2011 16:57:54 GMT

Hi, now I have a new problem with the same example as above.
The Dates I am using includes date + Time. Therefore I used:
Date(StartDate,'YYYY-MM-DD') as StartDate

Result:

The ActivityLink-table now skip the first date in the period, In other words:

StartDate=2008-10-17 EndDate=2011-08-02 ( The first) Date= 2008-10-18
When I try and use Floor(Date(StartDate,'YYYY-MM-DD')) instead I get 6 multiple rows in the table for each row.

I don´t know why, do anyone have an explanation to this?

Thanks!