topic Re: Interval match problem in QlikView

Interval match problem

Tue, 11 Dec 2012 13:32:13 GMT

Hello,

I don't know what is happening.

When i am applying interval match in qlikview. Script keep running for sometimes and when it is ended fetching all data.

It doesn't close loading window.

Just after sometime qlikview not responding.

what is the problem?

thanks

Lavi

Re: Interval match problem

RedSky001 — Tue, 11 Dec 2012 13:41:06 GMT

Someone here might have a crystal ball but you might be waiting for a while. for a qucker response can I suggest you provide more detail and perharps a sample of your script?

Mark

Re: Interval match problem

Tue, 11 Dec 2012 13:49:55 GMT

Here is the sample:

Table2:

Load

text([Resource No]) as [Resource No],

'0' as [Entry Type],

Date(Date) as Date,

Quantity,

'abc' as Source

Resident

Trackingdata;

Concatenate

LOAD text([Resource No]) as [Resource No],

[Entry Type],

Date(Date) as Date,

Quantity,

'def' as Source

FROM

SOMEQVDFILE;

Table1

LOAD

[Resource No],

OPERATINGBU as OBU ,

Date(FROMDATE) as FROMDATE,

Date(TODATE) as TODATE

FROM

Obu.qvd(qvd);

Join

IntervalMatch(Date) LOAD FROMDATE, TODATE Resident Table1;

Re: Interval match problem

christian77 — Tue, 11 Dec 2012 14:44:12 GMT

Hi, they are many examples in the community like this one.

http://community.qlik.com/thread/51150

Interval Match is a very good, effective and resolving function, but is difficult to set.

In the example above, there is a drop table (initial one).

If you don't drop that table, it probably starts a synthesis table, a big one, that’s why it lasts forever.

Check it out.

Good luck.

Re: Interval match problem

RedSky001 — Tue, 11 Dec 2012 14:53:38 GMT

I suspect multiple rows share the same start and end date, so it's duplicating rows when joining.

If it is you probably need a couple more steps (might be a smarter way but workes for me in the past... )

// Load uses DISTINCT otherwise you might introduce dupliate records. As records can share the same start and end date

IntervalMatch:

IntervalMatch(Date) LOAD DISTINCT FROMDATE, TODATE Resident Table1;

// make a composite key out of the start and end date which have been matched to indvidual dates.

MAP_Date:

Load

(FROMDATE & '+' & TODATE) AS EffDateKey

,Date

Resident IntervalMatch;

drop table IntervalMatch;

// Join the indvidual dates back to the main table using the composite key

BLAH:

NoConcatenate

load *

,(FROMDATE & '+' & TODATE) as EffDateKey

Resident Table1;

INNER join (BLAH)

load *

Resident MAP_Date;

Drop tables MAP_Date, Table1;

drop field EffDateKey;