Merge queries with similar dates

[Anonymous]

Hi Community, 

I have a problem with this as in, I don't know what's the easiest way to make this happen. Here it is the scenario:

In Table A i have the following data

orig	Dest	Travl From	Travel To	Expiry Date
XXX	YYY	1/11/2018	15/12/2018	10/07/2018
ABC	CDE	20/01/2019	5/04/2019	20/08/2018
ZZZ	AAA	30/04/2019	6/01/2019	10/05/2018

And in Table B this one:

Travel From	Travel To	Index
1/11/2018	15/12/2018	TP1
9/01/2019	5/04/2019	TP2
29/04/2019	6/01/2019	TP3

I have to merge the index into Table A if travel from and travel to are similar. The travel month and year shall be the same but the day of the month can be flexible. 

I thought that merging queries would be the easiest way but it didn't really work out

[v-yulgu-msft]

Hi @Anonymous,

"I have to merge the index into Table A if travel from and travel to are similar."

How should we understand “similar”? To reference to the "Index" column of Table B in Table A, you should provide a specific rule.

For example, if there existing some records in Table B like below:

19/01/2019	5/04/2019	TP4
21/01/2019	5/04/2019	TP5

Which "Index" should be added to below row in Table A?

ABC

CDE

20/01/2019

5/04/2019

20/08/2018

Best regards,

Yuliana Gu

[Anonymous]

Hi Yuliana, 

sorry for being inaccurate. What i mean is that the column index (so TP1,2,3) should be merged into the Table a IF Travl Form and Travel To are similar (say, there is a difference of +/-5 days for both).

I can make specific, in other words to merge only if these fields are matching but in this model a bit of flexibility would be great. 

Hope i clarified myself.

cheers

Alberto

[Anonymous]

Hi @Anonymous,

I have just extracted month and year from your both table column( Travel To) and just joined both tables.

Assuming that Month& Year of Travelto will be same for the both table. Then Just created a new column with RELATED Dax.

For your refernce i am attaching PBIX File here. Kindly see and revert me if any concern.

Best Regards,

Ravi

[Anonymous]

Hi Ravi, 

thank you for your feedback. I thought to do something like this, basically considering the month only despite the day of the month. Could be a solution. ATM i merged 2 query to 2 query as there are more fields to match: I am sure there is a better way to do this but at the moment this is what i came up with:

Again we have 2 tables:

Table A

Orig

Dest

cxr

Sale End

Travel from

Travel to

Sale Range Index

Sale End Index

XXX

YYY

AB

10/05/2018

1/01/2019

10/02/2019

TP1

SP1

XXX

YYY

AB

10/06/2018

1/01/2019

12/02/2019

TP1

SP1

XXX

YYY

AB

10/06/2018

1/03/2019

12/04/2019

TP2

SP2

TAble B

Orig

Dest

cxr

Sale End

Travel from

Travel to

Sale Range Index

Sale End Index

XXX

YYY

BC

8/05/2018

1/01/2019

11/02/2019

TP1

SP1

XXX

YYY

BC

8/05/2018

1/01/2019

12/02/2019

TP1

SP1

XXX

YYY

BC

10/06/2018

1/03/2019

12/04/2019

TP2

SP2

Merged: TABLE C

Orig

Dest

cxr

Sale End

Travel from

Travel to

Sale Range Index

Sale End Index

cxr

Sale End

Travel from

Travel to

Sale Range Index

XXX

YYY

AB

10/05/2018

1/01/2019

10/02/2019

TP1

SP1

BC

8/05/2018

1/01/2019

11/02/2019

TP1

SP1

XXX

YYY

AB

10/06/2018

1/01/2019

12/02/2019

TP1

SP1

BC

8/05/2018

1/01/2019

12/02/2019

TP1

SP1

XXX

YYY

AB

10/06/2018

1/03/2019

12/04/2019

TP2

SP2

BC

10/06/2018

1/03/2019

12/04/2019

TP2

SP2

This solution works theorethically but it's too strict (and complex) but the outcome that delivers is outstanding. 

Do you think i can modify my query in a way that only checks the month?

I'm trying to attach the .pbix file but I don't know how to do it

[Anonymous]

Hi @Anonymous,

Thank you for feedback.

Though i am not getting your actual requirement as you mentioned above.

i can modify my query in a way that only checks the month?

If you are asking that only month basis then yes you can.

For Your Concern i have a question and some suggestion.

1. Why do you need this type of data. It can be handle in StarSchema( Fact and Dimension).

2. You can handle it in SQL Query if data is coming from database.

Best Regrads,

Ravi

[Anonymous]

Hi Ravi, 

"i can modify my query in a way that only checks the month?

If you are asking that only month basis then yes you can"

in other words: as you've done, you changed the format of travel date from/to to extract the month and year through a calculated column. At the moment my model merges the 2 tables at query level so they only match if those fields are consistent. 

So don't know how to set up the query to re-script in order to extract month/year  accordingly. But in any case the month only could be too generic because if there are many difference days between date from table A and B that may be meaningless. 

Why do you need this type of data. It can be handle in StarSchema( Fact and Dimension)

Well this is part of a business process automation process and this is what the company provides. Don't know StarSchema

. You can handle it in SQL Query if data is coming from database.

It comes from a shared .xls file from a share point location.