Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
Anonymous
Not applicable
Author

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;

View solution in original post

7 Replies
swuehl
MVP
MVP

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

Anonymous
Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

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

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

Anonymous
Not applicable
Author

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
Anonymous
Not applicable
Author

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;