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

[SunilChauhan]

see the attached

hope this helps

[beunderf]

Dear Sunil,

Thanks for your input, but is not what I am looking for. One TransactionID should be joined to only one CarID depending on the Checkin/chekout time and station. Your join is just simply on checkin station.

The exact result after the join should be the folowing table:

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

[SunilChauhan]

sorry  Frank,

I believe soe thing you need to check,

i think there should be Cardir or transactionid common in both table

or something else

or someone helps

[rohit214]

Hey frank,

I attached modified your application,

Please chaek it out

i know the way i did may be not correct,

thanks and regard

rohit

[rwunderlich]

I think for this problem you want "Extended Interval Match". Take a look in the Ref Guide for details.

-Rob

http://robwunderlich.com

[beunderf]

Hi Rohit,

Thanks for your response. You hardcoded the result in a Expression 🙂

That gives the correct anwser indeed, but that is not what I am looking for.

I am looking for a way to dynamicly find the correct anwser.

Frank

[beunderf]

Hi Rob,

Thanks for your anwser!

I know how the 'normal' intervalmatch works. Now I am looking at the Extended syntax, but I don't get it yet.

I will try to find more information about it, but if you have some examples, feel free to share them with me 😉

Best Regards,

Frank

[rwunderlich]

I have some questions on the data. In your expected results, you show that Tran 2 took CarID 200. But it seems to me that with the Table B of 9:56 they would take CarID 100. Also in the Result table the checkin/checkout times for Tran 2 have changed, as they have for other Trans. It seems to me those values shouldn't change. Can you confirm for me if this is typo or if I'm misunderstanding?

-Rob

[beunderf]

Hi Rob,

You are completly right, I did some sorting in Excel before I posted tmy example informtation, but I forgot to sort the result table. This is result I expect:

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

Sorry about my error, this makes it a bit more logical 🙂