Forum Discussion
Create a Crossjoin Table with boolean condition
Sorry, you are right. This list is my sample data.
| Flight No | Carrier Code | Key | Dep Airport | Arr Airport | Dep Time | Arr Time | Weekday |
| 1 | AA | DXBJED | DXB | JED | 5:05 | 6:00 | Monday |
| 2 | AA | JEDCAI | JED | CAI | 8:05 | 10:30 | Monday |
| 3 | AA | JEDLHR | JED | LHR | 11:05 | 13:00 | Monday |
| 4 | AA | JEDDXB | JED | DXB | 15:05 | 17:00 | Monday |
And, I want this table to find connection time at the hub (Arr Airport1 & Dep Airport2).
I have conditions;
Two Carrier codes must be same,
Arr Airport Code1 and Dep Airport Code2 must be same (Thats why I called hub),
Dep Time2 is bigger than Arr Time1 and the difference between two is smaller than 12 hours.
Like this table;
| Flight No1 | Carrier Code1 | Key1 | Dep Airport1 | Arr Airport1 | Dep Time1 | Arr Time1 | Weekday1 | Flight No1 | Carrier Code2 | Key2 | Dep Airport2 | Arr Airport2 | Dep Time2 | Arr Time2 | Weekday |
| 1 | AA | DXBJED | DXB | JED | 5:05 | 6:00 | Monday | 2 | AA | JEDCAI | JED | CAI | 8:05 | 10:30 | Monday |
| 1 | AA | DXBJED | DXB | JED | 5:05 | 6:00 | Monday | 3 | AA | JEDLHR | JED | LHR | 11:05 | 13:00 | Monday |
| 1 | AA | DXBJED | DXB | JED | 5:05 | 6:00 | Monday | 4 | AA | JEDDXB | JED | DXB | 15:05 | 17:00 | Monday |
| 2 | AA | JEDCAI | JED | CAI | 8:05 | 10:30 | Monday | 5 | AA | DXBJED | CAI | JED | 5:05 | 6:00 | Monday |
| 2 | . | . | . | . | . | . | . | . | . | . | . | . | . | . | |
This is the problem.
My first table has 700k rows. So when I cartesian table with this code,
Filter(
Crossjoin(
Selectcolumns(
Data,
"Carrier1", Data[Carrier Code],
"Dep1", Data[Dep Airport],
"Arr1, Data[Arr Airport],
"ArrTime",Data[Arr Time]),
Selectcolumns(
Data,
"Carrier2", Data[Carrier Code],
"Dep2", Data[Dep Airport],
"Arr2, Data[Arr Airport],
"DepTime",Data[Dep Time])),
[carrier1] = [carrier2] && [Arr1] = [Dep2] && [DepTime] - [ArrTime] < 0.5 && 0 < [DepTime] - [ArrTime])
This is so slow.
Is there any possibility to rewrite this code using calculatetable or smth. Because if my conditions filtered before crossjoin I think it can be fast.
Thank you