Date Match Different Columns

[Anonymous]

Hello guys, 

I have a database composed by 4 columns.

 Column 1: Date

 Column 2: Dolar rate for that date (column 1)

 Column 3: Date

 Column 4:  Euro rate for that date (column 3)

I want to build a database composed only by Date, Dolar Rate and Euro Rate.

The problem is that the Date on Column 1 doenst match the Date on Column 3.

As an example, the Date 1 starts on year 2005 and Date 3 starts on year 2012.

I need to match those dates so the rates for Dolar and Euro are the respective for each date.

Thanks

[v-jingzhang]

Hi @Anonymous 

@BA_Pete 's solution should work. Have you tried that? If it is not what you want, please provide some expected result based on your sample data and explain how you want to match them.

As the dates in Column20 and Column23 are not continuous, maybe you also want to have Dolar rates and Euro rates for those missing dates in expected output?

Best Regards,
Community Support Team _ Jing

[BA_Pete]

Hi @Anonymous ,

Create a new query that is a column of contiguous dates from your first date to today's date.

Merge onto this list your rate table on [Date] = [Column20]. Expand Dollar.

Do it again on [Date] = [Column23]. Expand Euro.

Pete