topic get division for each request in QlikView

get division for each request

Mon, 09 May 2011 06:40:52 GMT

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

johnw — Mon, 09 May 2011 19:04:22 GMT

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

Tue, 10 May 2011 07:12:32 GMT

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

Tue, 10 May 2011 07:29:50 GMT

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

Re: get division for each request

Tue, 10 May 2011 13:49:08 GMT

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