Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join “Do More With Qlik” with Mike Tarallo to get a first-hand look at the New Authoring Experience, June 29th: REGISTER HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Interval match problem

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

4 Replies
RedSky001
Creator III
Creator III

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

Not applicable
Author

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
Partner
Partner

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
Creator III
Creator III

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;