Joining Tables Using IntervalMatch

[sreenivaskollur]

Hi Folks,

Could you please help me to get the solution for this?

I've two tables in my dataset.

Table1:

CLNDR_DT CURRENCY Temp_ID

10/2/2017 EUR 1

10/3/2017 CAD 2

10/4/2017 AUD 3

10/4/2017 USD 4

Table2:

CURRENCY USDRATE STARTDATE ENDDATE

EUR 1.1814 10/2/2017 10/2/2017

EUR 1.1733 10/3/2017 10/3/2017

EUR 1.1744 10/4/2017 10/4/2017

CAD 0.8018 10/2/2017 10/2/2017

CAD 0.79949 10/3/2017 10/3/2017

CAD 0.80083 10/4/2017 10/4/2017

AUD 0.7834 10/2/2017 10/2/2017

AUD 0.7827 10/3/2017 10/3/2017

AUD 0.7836 10/4/2017 10/4/2017

USD 1 10/2/2017 10/2/2017

USD 1 10/3/2017 10/3/2017

USD 1 10/4/2017 10/4/2017

Now, I would like to have two additional fields in Table1.

1. USDRATE

--Value for this field should be from Table2.USDRATE field where Table1.CURRENCY=Table2.CURRENCY and Table1.CLNDR_DT matches the intervals Table2.STARTDATE, Table2.ENDDATE.

2. EUR_USDRATE

--Value for this field should be from Table2.USDRATE field where Table2.CURRENCY = 'EUR' and Table1.CLNDR_DT matches the intervals Table2.STARTDATE, Table2.ENDDATE.

I've read some community links on IntervalMatch to achieve this, but can't figure out the exact code for getting this done. Please help me. Your help is very much appreciated. Thank you.

[Anil_Babu_Samineni]

PFA

[sreenivaskollur]

Thank you for your response. But, I'm expecting Table1 should look like this at the end,

Table1:

CLNDR_DT, CURRENCY, Temp_ID, USDRATE, EUR_USDRATE

10/2/2017, EUR, 1, 1.1814, 1.1814

10/3/2017, CAD, 2, 0.79949, 1.1733

10/4/2017, AUD, 3, 0.7836, 1.1744

10/4/2017, USD, 4, 1, 1.1744

];

[uacg0009]

Hi Sreenivas,

Hope below is what you want.

Thanks.

Aiolos