topic A Join with conditions in QlikView

A Join with conditions

beunderf — Mon, 26 Sep 2011 11:14:44 GMT

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

Re: A Join with conditions

SunilChauhan — Mon, 26 Sep 2011 11:43:09 GMT

see the attached

hope this helps

Re: A Join with conditions

beunderf — Mon, 26 Sep 2011 12:04:58 GMT

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

A Join with conditions

SunilChauhan — Mon, 26 Sep 2011 12:42:18 GMT

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

Re: A Join with conditions

rohit214 — Tue, 27 Sep 2011 04:54:15 GMT

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

A Join with conditions

rwunderlich — Tue, 27 Sep 2011 06:06:36 GMT

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

-Rob

http://robwunderlich.com

Re: A Join with conditions

beunderf — Tue, 27 Sep 2011 12:00:19 GMT

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

Re: A Join with conditions

beunderf — Tue, 27 Sep 2011 12:03:52 GMT

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

A Join with conditions

rwunderlich — Wed, 28 Sep 2011 01:29:37 GMT

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

A Join with conditions

beunderf — Wed, 28 Sep 2011 05:04:33 GMT

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 🙂

Re: A Join with conditions

qliksus — Thu, 29 Sep 2011 07:53:46 GMT

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

A Join with conditions

rwunderlich — Thu, 29 Sep 2011 17:59:15 GMT

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

Re: A Join with conditions

beunderf — Mon, 03 Oct 2011 07:20:31 GMT

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

Re: A Join with conditions

rwunderlich — Mon, 03 Oct 2011 15:56:00 GMT

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

;

Re: A Join with conditions

beunderf — Tue, 04 Oct 2011 08:44:19 GMT

Rob, this helps me a lot!

Thanks very much!