Forum Discussion
Match two tables with multiple criteria
Hi,
I'm facing a problem to map values between two tables.
Basically there are two tables, one is "Voyage" and the other one is "Job", and my objective is to see how many jobs we didn't manage to get per port.
For example, in the "Voyage" table, Vessel "A", Destination Port "ASD" and sailling date is on Jan 6th 2019, and in the "Job" table, it needs to look up the same Vessel which is "A", then look up in Loading Port "ASD", and if the two condition matched, the third criteria is the Job Perform date in "Job" table must not be more than two months (eg 60 days) than the "Sailling date" in 'Voyage" table.
If any of the 3 criteria is not matched, it counts as "one" opportunity.
In the end, I want to have an analysis on how many opportunities per Destination Port in the "Voyage" Table.
Can anyone please help me with this? I have listed below the two tables for your reference.
Thanks!
Voyage Table:
| Vessel | Destination Port | Sailing Date (mm/dd/yyyy) |
| A | ASD | 1/6/2019 |
| B | FSD | 3/5/2019 |
| C | KLD | 5/6/2019 |
| D | DFR | 3/4/2019 |
| A | ACF | 2/5/2019 |
| B | ASD | 4/5/2019 |
| C | FSD | 3/17/2019 |
| D | KLD | 3/29/2019 |
| E | DFR | 5/1/2019 |
| F | ACF | 3/9/2019 |
| B | DFR | 4/12/2019 |
| C | ACF | 5/4/2019 |
| D | ASD | 3/21/2019 |
| E | FSD | 3/23/2019 |
| F | KLD | 3/14/2019 |
| C | FSD | 1/9/2019 |
| D | KLD | 2/16/2019 |
| A | DFR | 2/9/2019 |
| B | ACF | 5/3/2019 |
| C | DFR | 4/14/2019 |
| D | ACF | 4/4/2019 |
| E | ASD | 4/21/2019 |
| F | FSD | 3/4/2019 |
| B | KLD | 2/22/2019 |
| C | FSD | 4/4/2019 |
| F | KLD | 2/28/2019 |
| B | DFR | 3/6/2019 |
| C | ACF | 4/13/2019 |
| D | DFR | 4/3/2019 |
| E | ACF | 4/20/2019 |
| F | ASD | 3/14/2019 |
| C | FSD | 2/26/2019 |
| D | KLD | 2/18/2019 |
Job Table:
| Vessel | Loading Port | Job perform date (mm/dd/yyyy) |
| A | ASD | 2/3/2019 |
| B | FSD | 4/6/2019 |
| C | KLD | 8/4/2019 |
| D | DFR | 7/4/2019 |
| E | DFR | 6/1/2019 |
| F | ACF | 5/9/2019 |
| B | ACF | 6/9/2019 |
| C | FSD | 7/1/2019 |
| D | ASD | 4/1/2019 |
| E | FSD | 5/4/2019 |
| F | KLD | 3/23/2019 |
| C | FSD | 3/4/2019 |
| D | KLD | 3/1/2019 |
| A | ACF | 3/4/2019 |
| B | FSD | 4/4/2019 |
| C | ASD | 8/4/2019 |
| D | FSD | 7/4/2019 |
| E | KLD | 6/1/2019 |
Hi Anonymous ,
Sorry for the delay.
Please modify the measures as below:
Measure = VAR a = CALCULATE ( MIN ( Merge1[Job perform date] ), FILTER ( ALL ( Merge1 ), Merge1[Vessel] = MAX ( Merge1[Vessel] ) && Merge1[Destination Port] = MAX ( Merge1[Destination Port] ) ) ) VAR b = IF ( ISBLANK ( MAX ( Merge1[Job perform date] ) ), BLANK (), IF ( MAX ( Merge1[Sailing Date] ) > MAX ( Merge1[Job perform date] ), "NA", IF ( MAX ( Merge1[Job perform date] ) <> a && MAX ( Merge1[Sailing Date] ) < a, "NA", a ) ) ) RETURN IF ( ISBLANK ( b ), 1, IF ( b = "NA", 0, IF ( DATEDIFF ( MAX ( Merge1[Sailing Date] ), MAX ( Merge1[Job perform date] ), MONTH ) > 2, 1, 0 ) ) )Measure 2 = SUMX(Merge1,[Measure])Result would be shown as below:
BTW, Pbix as attached, hopefully works for you.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
13 Replies
Hi Anonymous ,
I'm working on your data and I want to know how we deal with the duplicate data in Voyage table and Job table?
For Example:
Voyage Table:
Vessel Destination Port Sailing Date (mm/dd/yyyy) C FSD 3/17/2019 C FSD 1/9/2019 C FSD 4/4/2019 Job Table:
Vessel Loading Port Job perform date (mm/dd/yyyy) C FSD 7/1/2019 C FSD 3/4/2019 Best Regards,
Jay
Hi Anonymous ,
Thanks for helping.
It's not really duplicated rows because the Sailing date in Voyage table and Job performed date in Job table is different.
For example, in the Voyage table, Vessel C could depart to Destination Port FSD a few times across the year.
Similarly, we could do the loading job for Vessel C a few times in a year.
I understand that there could be a problem like below, it's just an illustration and not in my file:
Voyage Table:
Vessel Destination Port Sailing Date (mm/dd/yyyy) C FSD 3/17/2019 C FSD 2/9/2019 C FSD 5/1/2019 Job Table:
Vessel Loading Port Job perform date (mm/dd/yyyy) C FSD 4/1/2019 In the example above, based ont he criteria, the Job performed on April 1st 2019 which mapped both Sailing date on March 17th and Feburary 9th 2019 (less than 60 days). In such a case, they are not considered an Opportunity. However, the Sailing date is May 1st 2019 but there is no job performed within 60 days on the same vessel same port, so this is considered as an Opportunity.
Result (by creating a new column):
Vessel Destination Port Sailing Date (mm/dd/yyyy) Opportunity C FSD 3/17/2019 No C FSD 2/9/2019 No C FSD 5/1/2019 Yes Thanks again!
Hi @Setname ,
In you scenario, we can use Merge Queries feature in Query Editor, please check following steps as below:
1. Merge these two tables as a new table and expand table:
2. Click Close&Apply and create measures:
Measure = IF(ISBLANK(MAX(Merge1[Job.Job perform date (mm/dd/yyyy)])),1,IF(DATEDIFF(MAX(Merge1[Sailing Date (mm/dd/yyyy)]),MAX(Merge1[Job.Job perform date (mm/dd/yyyy)]),MONTH)>2,1,0))
Measure 2 = SUMX(Merge1,[Measure])
3. Result would be shown as below:
BTW, Pbix as attached, hopefully works for you.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
venalMemorable Member
Anonymous
I tried to re-pro the issue from my end and uploaded the file.
3rd creteria, i am not clear. so tried to do it upto days wise data.
If you have any concerns, please let us know.
If this post helps, then please consider Accept it as the solution to help the other members find it more
If this post was helpful may I ask you to mark it as solution and click on thumb symbol?Thanks venal , it's a good idea but the base we should use is Voyage table not Job table to do the merge.
However, Merge method doesnt really solve the issue because it will create duplicate rows after merging.
For example as below, it will create duplicate two rows (Vessel B, Port FSD and same Sailing Date), however my objective is to only showing the earliest Job perform date which is April 4th 2019, not together with April 6th 2019. Do you have any idea how to resolve this? Thansks.
Vessel Destination Port Sailing Date (mm/dd/yyyy) Job.Vessel Job.Loading Port Job.Job perform date (mm/dd/yyyy) B FSD 3/5/2019 B FSD 4/6/2019 B FSD 3/5/2019 B FSD 4/4/2019