Getting a row level operation by using two different tables

[sahubibhuti45]

Hi Folks,

I have 2 tables like

Loan
Party name	currency	date	org_amount
john	usd	15th jan	30
joy	usd	16th jan	40
chandler	aud	20th jan	20
monika	eur	14th feb	40
phoebe	din	20th feb	20

another table having 

Currency History
Date	currency	conversion amount
20th jan	usd	81
20th jan	aud	84
20th jan	din	82
20th jan	eur	84
21st jan	usd	87
21st jan	aud	83
21st jan	din	81
21st jan	eur	84
22nd jan	usd	92
22nd jan	aud	85
22nd jan	din	82
22nd jan	eur	84

I wanted to give a date slicer as an input date from the rate history table i.e. 2nd table and want to calcualte the amount by multiplying the org_amount * conversion rate for each party. I have tried joining both the tables but i am getting huge number of rows.

whated to get an output like this. based on user input date

Loan
Party name	currency	date	org_amount	org_amount * conversion amount

 Thanks

[Daniel29195]

@sahubibhuti45 

the calculation should be base on the currency =  currenct and date  = date ? 

[sahubibhuti45]

Hi @Daniel29195 ,Date is from 2nd table because the values are varying and we need to chhose a date,depending upon input date it will pick up the values from the 2nd table and multiply the origional amount

[Daniel29195]

@sahubibhuti45 

ok. 

but if let us say you choose 20 of january, 

then all the data of the table Loan, should be multipled by the conversion amount of 20 of jan , respecting the currencies. 

is that right ? 

[sahubibhuti45]

yes @Daniel29195 , Absolutely correct.

[Daniel29195]

 @sahubibhuti45 

1. create a new column in both table as follow : 

curreny_col_name & "|" & date 

nB:  make sure that the date is written in the same format in both tables. 

2.now you can link the 2 tables, on this column created . (  it should be a 1 to many relationship going from currency history table (1)  to loan table ( many ) 

3. 

from here, things should be easy. 

you can create a new column in the loan table as  : related( curreny_history_table_name[ conversion amount col name] ) 

now you have the conversion amount in your loan table. 

hope this helps .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[sahubibhuti45]

Yes @Daniel29195 , Thats correct.

[sahubibhuti45]

tried with solutions but unable to take 2 different columns from 2 different tables