Interval match problem

[#1]

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

[RedSky001]

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

[#3]

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;

[christian77]

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.

[RedSky001]

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;