Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

darrin_pilkingt
Contributor II

IntervalMatch only giving cartesian join

I have been reading Henric Cronström's white paper and posts on IntervalMatch() and I am still not getting what I believe I should.


I am trying to link each event based on the EventStartDtTm between an Agents ShiftStart and ShiftEnd.  I keep getting, using the example below, Agent 995's Events displaying with 996 and vice versa.  It seems like there needs to be a way to link the AgentID in with this.


There is also a second phase of this I would like to do which is merge this into a single table that can be pushed to a QVD but I can't get this piece right yet.

Example Employee Daily Shift Intervals:

AgentIDEventDtShiftStartShiftEnd
99512/3/20138:00:3216:19:55
99612/3/20138:06:2617:00:55



Daily Events to match within the shift:

AgentIDEventStartDtTmEventEndDtTmReleaseIDReleaseCode
99512/3/2013 10:53:2112/3/2013 11:03:00 AM315 min Break
99512/3/2013 12:24:3712/3/2013 12:24:39 PM6Work
99512/3/2013 13:00:4712/3/2013 1:28:04 PM4Lunch Break
99512/3/2013 15:09:1612/3/2013 3:12:59 PM315 min Break
99612/3/2013 8:41:1912/3/2013 8:43:26 AM6Work
99612/3/2013 9:39:1712/3/2013 9:43:22 AM6Work

Thank you for any help with this.

1 Solution

Accepted Solutions
darrin_pilkingt
Contributor II

Re: IntervalMatch only giving cartesian join

I am loving this conversation with myself.

Swuehl, your mention of extended intervalmatch functionality helped greatly.  I did not realize what I was trying to do fell into the same category as a slowly changing dimension.  What I am doing isn't but it did show me that creating certain keys with the AgentID linked things without the entire cartesian product I was getting before.

All 6 months worth of records now takes 12 seconds.

/*******[ Load agent daily shift with Interval ]*******/

AgentShiftInterval:

LOAD

     AgentID & ' | ' &  EventStartDtTm & ' | ' & EventEndDtTm as [AgentID+Interval],

     EventStartDtTm as ShiftStart,

     EventEndDtTm as ShiftEnd,

     EventDt,

     AgentID,

     AgentName,

     Duration as ShiftDuration

FROM

[..\TransformQVD\AgentShiftSummary.qvd]

(qvd);

/*******[ Load agent Release Code events ]*******/

ReleaseCodes:

LOAD

  AgentID & ' | ' & EventStartDtTm as [AgentID+TransDt],

     AgentID as TmpAgentID,

     EventStartDtTm,

     EventEndDtTm,

     Duration,

     ReleaseID,

     ReleaseCode

FROM

[..\TransformQVD\AgentReleaseCodes.qvd]

(qvd);

/*******[ Create Bridge ]*******/

TmpBridgeTable:

IntervalMatch(EventStartDtTm, TmpAgentID)

Load Distinct ShiftStart, ShiftEnd, AgentID as TmpAgentID

Resident AgentShiftInterval;

/*******[ Join to drop the Bridge ]*******/

Left Join(ReleaseCodes)

Load

  TmpAgentID, EventStartDtTm,

  TmpAgentID & ' | ' &  ShiftStart & ' | ' & ShiftEnd as [AgentID+Interval]

Resident TmpBridgeTable;

DROP Field TmpAgentID;

DROP Table TmpBridgeTable;

/*******[ Join to flatten out my structure prior to QVD storage ]*******/

Left Join(AgentShiftInterval)

Load [AgentID+Interval],

     EventStartDtTm,

     EventEndDtTm,

     Duration,

     ReleaseID,

     ReleaseCode

Resident ReleaseCodes;

DROP Table ReleaseCodes;

7 Replies
MVP
MVP

Re: IntervalMatch only giving cartesian join

How does your script look like? You are using the extended version of IntervalMatch function, aren't you?

darrin_pilkingt
Contributor II

Re: IntervalMatch only giving cartesian join

Extended version?  Possibly.  Here is what I have. 

In the Events table I have the AgentID that I rename to tAgentID to prevent a synthetic key and for testing to see if the correct events for the agent are coming through or if there was a way I could then link back flatten this out to a single table to store in the QVD.

Interval:

LOAD

     RecNo() as IntervalID,

     EventStartDtTm as ShiftStart,

     EventEndDtTm as ShiftEnd,

     EventDt,

     AgentID,

     AgentName

FROM

AgentShiftSummary.qvd (qvd);

Events:

LOAD

     EventStartDtTm,

     EventEndDtTm,

     Duration,

     AgentID as tAgentID,

     ReleaseID,

     ReleaseCode

FROM

AgentReleaseCodes.qvd (qvd);

Tmp_BridgeTable:

Load distinct EventStartDtTm Resident ReleaseCodes ;

Join

Load IntervalID, ShiftStart, ShiftEnd Resident AgentShiftInterval ;

BridgeTable:

Load distinct EventStartDtTm, IntervalID

Resident Tmp_BridgeTable

Where ShiftStart <= EventStartDtTm and EventStartDtTm < ShiftEnd ;

Drop Table Tmp_BridgeTable ;

darrin_pilkingt
Contributor II

Re: IntervalMatch only giving cartesian join

I think I figured it out.  I linked the Bridge values to my Intervals table and then my Events to my Intervals.

IntervalJoin:

Left Join(AgentShiftInterval)

Load

  EventStartDtTm,

  IntervalID

Resident BridgeTable;

DROP Table BridgeTable;

EventJoin:

inner Join(AgentShiftInterval)

LOAD     

     tAgentID as AgentID,

     EventStartDtTm,

     EventEndDtTm,

     Duration,

     ReleaseID,

     ReleaseCode

Resident ReleaseCodes;

DROP Table ReleaseCodes;

darrin_pilkingt
Contributor II

Re: IntervalMatch only giving cartesian join

Odd, when I do these joins to flatten out the table with my smaller sample of data it works fine.  When I use my actual data loaded it runs and then errors with a

"General Script Error"


Does anyone have any thoughts?


Thank you.

MVP
MVP

Re: IntervalMatch only giving cartesian join

At which point do the error occur? Can you post your document log?

darrin_pilkingt
Contributor II

Re: IntervalMatch only giving cartesian join

I am rerunning based on an error I found. 

My intervals extend over a day.  Is there a good way to overlap the interval process so I do not need to run this for such a long period each day?

Below would be an example of an agent logging in at 8pm at night on the 12/7 and the shift ends the following day. The app runs the following morning and does not pickup the end of the shift or events that fall into 12/8.

Is there a good way to load them and add these on the 12/9 following days load?  This way my intervalmatch bridge before the join to remove it would be much smaller.  I hope this makes sense.

Example:

Example Employee Daily Shift Intervals:

AgentIDEventDtShiftStartShiftEnd
99512/7/201322:00:32



Daily Events to match within the shift:

AgentIDEventStartDtTmEventEndDtTmReleaseIDReleaseCode
99512/7/2013 22:53:2112/7/2013 23:03:00315 min Break
99512/7/2013 22:24:3712/7/2013 23:24:396Work
99512/7/2013 23:20:474Lunch Break
darrin_pilkingt
Contributor II

Re: IntervalMatch only giving cartesian join

I am loving this conversation with myself.

Swuehl, your mention of extended intervalmatch functionality helped greatly.  I did not realize what I was trying to do fell into the same category as a slowly changing dimension.  What I am doing isn't but it did show me that creating certain keys with the AgentID linked things without the entire cartesian product I was getting before.

All 6 months worth of records now takes 12 seconds.

/*******[ Load agent daily shift with Interval ]*******/

AgentShiftInterval:

LOAD

     AgentID & ' | ' &  EventStartDtTm & ' | ' & EventEndDtTm as [AgentID+Interval],

     EventStartDtTm as ShiftStart,

     EventEndDtTm as ShiftEnd,

     EventDt,

     AgentID,

     AgentName,

     Duration as ShiftDuration

FROM

[..\TransformQVD\AgentShiftSummary.qvd]

(qvd);

/*******[ Load agent Release Code events ]*******/

ReleaseCodes:

LOAD

  AgentID & ' | ' & EventStartDtTm as [AgentID+TransDt],

     AgentID as TmpAgentID,

     EventStartDtTm,

     EventEndDtTm,

     Duration,

     ReleaseID,

     ReleaseCode

FROM

[..\TransformQVD\AgentReleaseCodes.qvd]

(qvd);

/*******[ Create Bridge ]*******/

TmpBridgeTable:

IntervalMatch(EventStartDtTm, TmpAgentID)

Load Distinct ShiftStart, ShiftEnd, AgentID as TmpAgentID

Resident AgentShiftInterval;

/*******[ Join to drop the Bridge ]*******/

Left Join(ReleaseCodes)

Load

  TmpAgentID, EventStartDtTm,

  TmpAgentID & ' | ' &  ShiftStart & ' | ' & ShiftEnd as [AgentID+Interval]

Resident TmpBridgeTable;

DROP Field TmpAgentID;

DROP Table TmpBridgeTable;

/*******[ Join to flatten out my structure prior to QVD storage ]*******/

Left Join(AgentShiftInterval)

Load [AgentID+Interval],

     EventStartDtTm,

     EventEndDtTm,

     Duration,

     ReleaseID,

     ReleaseCode

Resident ReleaseCodes;

DROP Table ReleaseCodes;

Community Browser