Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Intervalmatch issue

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;
Tags (1)
4 Replies
Not applicable

Intervalmatch issue

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

Not applicable

Intervalmatch issue

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;

avastani
Contributor II

Intervalmatch issue

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;
Not applicable

Intervalmatch issue

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!

Community Browser