IntervalMatch only giving cartesian join

[Anonymous]

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:

AgentID	EventDt	ShiftStart	ShiftEnd
995	12/3/2013	8:00:32	16:19:55
996	12/3/2013	8:06:26	17:00:55

Daily Events to match within the shift:

AgentID	EventStartDtTm	EventEndDtTm	ReleaseID	ReleaseCode
995	12/3/2013 10:53:21	12/3/2013 11:03:00 AM	3	15 min Break
995	12/3/2013 12:24:37	12/3/2013 12:24:39 PM	6	Work
995	12/3/2013 13:00:47	12/3/2013 1:28:04 PM	4	Lunch Break
995	12/3/2013 15:09:16	12/3/2013 3:12:59 PM	3	15 min Break
996	12/3/2013 8:41:19	12/3/2013 8:43:26 AM	6	Work
996	12/3/2013 9:39:17	12/3/2013 9:43:22 AM	6	Work

Thank you for any help with this.

[swuehl]

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

[Anonymous]

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 ;

[Anonymous]

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;

[Anonymous]

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.

[swuehl]

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

[Anonymous]

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:

AgentID	EventDt	ShiftStart	ShiftEnd
995	12/7/2013	22:00:32

Daily Events to match within the shift:

AgentID	EventStartDtTm	EventEndDtTm	ReleaseID	ReleaseCode
995	12/7/2013 22:53:21	12/7/2013 23:03:00	3	15 min Break
995	12/7/2013 22:24:37	12/7/2013 23:24:39	6	Work
995	12/7/2013 23:20:47		4	Lunch Break

[Anonymous · Accepted solution]

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;