Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

1 Solution

Accepted Solutions
Not applicable

Re: get division for each request

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

4 Replies
MVP
MVP

Re: get division for each request

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.

Not applicable

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?

Not applicable

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

Not applicable

Re: get division for each request

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

Community Browser