4 Replies Latest reply: May 10, 2011 1:02 PM by Reignfire RSS

    get division for each request

      Hi,

       

      I'm trying to determine for which division the request is made. (inline example in attachment)

       

      |__________|____________|__________________|______________|____________|____________|
      START_TS REQUEST_TS 1 TRANSFER_TS 1  REQUEST_TS 2  TRANSFER_TS 2  REQUEST_TS 3 END_TS

       

      So if the request is made between start and transfer1 (REQUEST_TS 1) it has the startDivision ( CB1 ),
      if it's between Transfer 1 and 2 (REQUEST_TS 2) it's the division in the transfertable for the first transfer (CB 5).
      if it's between Transfer 2 and the END (REQUEST_TS 3) it's the division in transfertable for the second transfer( back to CB1).

      It's also possible there are no transfer then it's the startDivision.

      How do i create a table with these tables were i can give each request a correct division and will it be possible to do this on
      big files?

       

      Pls help thx

        • Re: get division for each request
          John Witherspoon

          If I've understood the question, the trick isn't so much the intervalmatch itself, but rather in setting up a table structure to use with the intervalmatch.  I think you need to generate one that looks like this:

           

          DivisionAssignment:
          CODE, Start, End, Divison
          "1000,00", 11/03/2007 15:13:00, 12/03/2007 12:14:59:99, CB1
          "1000,00", 12/03/2007 12:15:00, 13/03/2007 08:36:59:99, BE5
          "1000,00", 13/03/2007 08:37:00, 14/03/2007 15:08:00:00, CB1

           

          Once you have that, it's easy enough to do the intervalmatch:

           

          LEFT JOIN (REQUEST)
          INTERVALMATCH (REQUEST_TS,CODE)
          LOAD
          Start
          ,End
          ,CODE
          RESIDENT DivisionAssignment
          ;
          LEFT JOIN (REQUEST)
          LOAD *
          RESIDENT DivisionAssignment
          ;
          DROP TABLE DivisionAssignment
          ;

           

          To build the table, you're going to need to combine the data from your START and TRANSFER tables.  I could probably figure it out, but I'm short on time today, and hoping you can figure it out on your own.

            • get division for each request

              That's indeed the way i want it to be! Now i need to find how to combine them correctly. So i need to start with the startTS from the START table and then depending on how much transfers there are i have to build time periods.

               

              START:

              ID,

              startTS,

              endTS,

              startDivision

               

              TRANSFER:

              ID,

              transferTS,

              transferDIVISION

               

              NEW_TABLE:

              ID       startTS       -->     transferTS 1   startDivision
              ID       transferTS 1  --> transfer TS 2   transferDIVISION 1
              ID       transfer TS 2  -->  transfer TS 3  transferDIVISION 2

              ID       transfer TS 3 -->  endTS            transferDivsion 3

               

              I probably need to use for loops but how can i say, combine startTS with transferTS 1?

               

                • get division for each request

                  I mean i can use this:

                  Count:
                  Load distinct
                      TRANSFER_TS as transfer
                  resident TRANSFER;

                  //count number of transfers
                  LET nrTRANSFERS = noofrows('Aantal')-1;

                      FOR rowStatus = 0 TO $(nrTRANSFERS)
                          LET S = peek('transfer',rowStatus,'Count');
                             
                              NEW:
                     LOAD
                      ID,
                      START_TS,    //i already joined START with TRANSFER so i can use them both
                        TRANSFER_TS
                     RESIDENT TRANSFER
                     WHERE TRANSFER_TS = '$(S)'
                              order by ID ASC, TRANSFER_TS ASC;     
                      NEXT

                   

                  But that's only good for the first loop cause the next has to be TRANSFER_TS 1 with TRANSFER_TS 2 and not with START_TS...