A Join with conditions

[beunderf]

Hello everybody,

I have a question. I want to join data between tables using some conditions or rules.

I have two tables. One table gives me the schedule of a bus:

Table A:

CarID	Station sequence	Direction	Station	Departure time
100	1	South	Amsterdam	9:45:00
100	2	South	Utrecht	9:57:00
100	3	South	Veenendaal	10:14:00
100	4	South	Arnhem	10:29:00
100	5	South	Nijmegen	10:40:00
200	1	South	Amsterdam	9:55:00
200	2	South	Utrecht	10:07:00
200	3	South	Veenendaal	10:24:00
200	4	South	Arnhem	10:39:00
200	5	South	Nijmegen	10:50:00
300	1	North	Nijmegen	9:45:00
300	2	North	Arnhem	9:57:00
300	3	North	Veenendaal	10:14:00
300	4	North	Utrecht	10:29:00
300	5	North	Amsterdam	10:40:00

The other gives me chekin and checkout times and station of a passanger:

Table B:

TransactionID	checkin	checkout	checkin station	chekout station
1	9:55:00	10:31:00	Utrecht	Arnhem
2	9:56:00	10:31:00	Utrecht	Arnhem
3	9:57:00	10:33:00	Utrecht	Arnhem
4	10:03:00	10:42:00	Utrecht	Arnhem
5	10:04:00	10:43:00	Utrecht	Arnhem
6	10:05:00	10:45:00	Utrecht	Arnhem

Now I want to join these to tables so that I now in which bus the passenger took. Expected result:

TransactionID	checkin	checkout	checkin station	chekout station	Used CarID
1	9:55:00	10:31:00	Utrecht	Arnhem	100
2	10:05:00	10:45:00	Utrecht	Arnhem	200
3	9:56:00	10:31:00	Utrecht	Arnhem	100
4	9:57:00	10:33:00	Utrecht	Arnhem	100
5	10:04:00	10:43:00	Utrecht	Arnhem	200
6	10:03:00	10:42:00	Utrecht	Arnhem	200

Extra info for the join:

Checkin time <= departure time

Checkout time => departure time of checkout station

The checkin station has a lower Station Sequence number as the Checkout station.

Does anyone know how to achieve this with Qlikview scripting. At this point, I have no idea, because I believe it not possible to compare fields of table A en Table B before or when the join is made? Who can help me?

I added a Qlikview file with the data

Best regards,

Frank

[qliksus]

Hi frank,

As per ur requirement for transactionid = 1 there will be 3 cardid matching the condition

100,200,300 but in ur expected result u need to see only 100

and incase of transaction id = 4 mactching records will be 200 and 300 but in ur expected result

u want to see only 200 so indirectly u are taking the minimum value after the result for each transaction id

if that is the case then see the attached file if i understood u wrongly then let me know

[rwunderlich]

Frank,

Yes, I think Extended IntervalMatch may do the trick. The basic form

IntervalMatch(DepartureTime) LOAD checkin, checkout RESIDENT TableB;

will link up the cars with trips in TableB. You get a link whenever DepartureTIme is between checkin and checkout. But it does not consider the station, so will link too many cars. The extended form simply adds another key -- Station -- that should be included as part of the match criteria.

IntervalMatch(DepartureTime, Station) LOAD checkin, checkout, checkinStation RESIDENT TableB;

So you'll get link where the time range matches AND Station=checkinStation.

-Rob

http://robwunderlich.com

[beunderf]

Rob,

Thanks a lot for your explanation. I understand now how it should work, but I don't get it to work. If  I use Test_A intervalmatch I get, as expected, a result with to many results, but it worked.

If I use extended intervalmatch Test_B, I get NO error, but als NO result. Do you have any idea what I am doing wrong. I added a qvw file. In the script on tab DATA, I scripted both ways.

Test_A:

IntervalMatch([Departure time]) LOAD checkin, checkout RESIDENT Transactions;

Test_B:

IntervalMatch([Departure time], Station) LOAD checkin, checkout, [checkin station] RESIDENT Transactions;

Your help is highly appreciated!!

Frank

[rwunderlich · Accepted solution]

You weren't matching beacuse I forgot to tell you that [checkin station] had to be called Station for the match.

There was still a problem where a  [Departure Time] matches too many trains, but I added some min logic following to get keep only the first match. Hopefully this will move you forward.

-Rob

INNER JOIN (Transactions)

IntervalMatch([Departure time], Station) LOAD checkin, checkout, [checkin station] as Station RESIDENT Transactions;

INNER JOIN (Transactions)

LOAD

          TransactionID,

          min([Departure time]) as [Departure time]

RESIDENT Transactions

GROUP BY TransactionID

;

[beunderf]

Rob, this helps me a lot!

Thanks very much!