Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
I managed to make it work with some great help from a friend of mine!
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.
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?
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...
I managed to make it work with some great help from a friend of mine!