A loop is always a problem you can solve, always, if not, how would your brain understand the way your data process ?
I see a lot of fields used for this data model and a lot of names which are quite the same, what is the real difference between the CURRENCYRATES and PolicyCurrencyRates tables ? Why do you need the POLICY table if LOCATION table is already linked with currencytargets tables ?
step by step :
- do not load the fields you don't need
- describe us the link between the fact table and exchange rate,how the exchange rate is linked to it
Ok I understand your problem, you need to put both tables into only one table.
This script will do it (see below). And then in your app, when you want to multiply a measure by an exchange rate, you will have to choose on what "type" of exchange rate you want the measure to be. That way you only have one field currencytarget but your measures will choose the right "type" of currency.
In a measure for example that would be :
The script :
// LOCATION :
'Curr|' & locationPremiumCurrency as LinkCurrencyRates,
'Policy|' & PolicyID as LinkPolicyRates,
locationPremiumCurrency&'|'&PolicyID as %ID_Fact_Bridge
LinkCurrencyRates as %Currency_Link,
'Curr' as Type
LinkPolicyRates as %Currency_Link,
'Policy' as Type
// Policy Rates
PolicyExcRate as ExchangeRate
'Policy|' & PolicyID as %Currency_Link,
'Policy' as TypeRate
Drop field policyPremiumCurrency;
'Curr|' & locationPremiumCurrency as %Currency_Link,
'Curr' as TypeRate