topic IntervalMatch only giving cartesian join in QlikView

IntervalMatch only giving cartesian join

Anonymous — Fri, 27 Dec 2013 23:06:05 GMT

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.

Re: IntervalMatch only giving cartesian join

swuehl — Sat, 28 Dec 2013 00:47:03 GMT

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

Re: IntervalMatch only giving cartesian join

Anonymous — Mon, 30 Dec 2013 18:07:12 GMT

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 ;

Re: IntervalMatch only giving cartesian join

Anonymous — Mon, 30 Dec 2013 18:50:43 GMT

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;

Re: IntervalMatch only giving cartesian join

Anonymous — Tue, 31 Dec 2013 17:56:39 GMT

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.

Re: IntervalMatch only giving cartesian join

swuehl — Tue, 31 Dec 2013 18:11:16 GMT

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

Re: IntervalMatch only giving cartesian join

Anonymous — Tue, 31 Dec 2013 19:28:52 GMT

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

Re: IntervalMatch only giving cartesian join

Anonymous — Tue, 31 Dec 2013 21:18:46 GMT

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;