get division for each request

[#1]

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

[johnw]

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.

[#3]

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?

[#4]

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

[#5 · Accepted solution]

I managed to make it work with some great help from a friend of mine!