Merge Join - Latest date when value is missing

[AndyR123]

Hi Community,

i do have a problem with a currency issue. I select the data with RestApi Calls.

In one table there are Sales Data in the currency of the customer, in the other table there is the currency.

Now i do have to calculate the Value of the data of the Sales Data (Value * Currency). I do only have currency data for Monday -Friday.  Saturday and Sunday is missing because there are no currency rates for the weekend.

I join the Currency Table into the Sales Table via merge join with the column join key. Now of course the rate for 25.1.is missing.  So is there a was to get the latest (the value from the latest date) currency value? In this case it would be the USD rate for 24.01.2020 for the sales entry from 25.01.2020. If there is a complete ther way to reach the target i would be happy, too 🙂

Thanks a lot

Andy

[v-yuta-msft]

@AndyR123 ,

Why not use left-outer join or full-outer join in the "join kind" option?

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AndyR123]

Hi, thank you for you response.

A full outer join won't help because there is no currency rate for 25.01.

Thank a lot

Andy