7 Replies Latest reply: Dec 31, 2013 4:19 PM by Darrin Pilkington RSS

    IntervalMatch only giving cartesian join

    Darrin Pilkington

      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.

        • Re: IntervalMatch only giving cartesian join
          Stefan Wühl

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

            • Re: IntervalMatch only giving cartesian join
              Darrin Pilkington

              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
                  Darrin Pilkington

                  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
                      Darrin Pilkington

                      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
                          Stefan Wühl

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

                            • Re: IntervalMatch only giving cartesian join
                              Darrin Pilkington

                              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
                                • Re: IntervalMatch only giving cartesian join
                                  Darrin Pilkington

                                  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;