Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Not applicable

Multiple currencies create circular reference

Hi All,

I was wondering if anyone could help me.

I have a data model with 2 main tables

Location and Policy as shown in the sample data model below

in these I have 2 fields where is stored the currency locationPremiumCurrency and policyPremiumCUrrency.

The filed Currency target is the currency that the user wants to view the data. meaning that no matter the currency in the record, when the user selects USD, all the values will be displayed as USD. So this column (CurrencyTarget will be a filter object)

How can I remove the circular reference keeping the unique column?

Could I separate the tables and build a macro to connect the 2?

Thanks in advance for your help.

1 Solution

Accepted Solutions
Highlighted
Not applicable

Re: Multiple currencies create circular reference

Hi All,

I managed to do it with set analysis.

Sum({<PolicyCurrencyTarget=LocationcurrencyTarget>} PolicyPremium *PolicyExchangeRate)

this way i keep the exchange rates separated and I can link them in the measure

View solution in original post

10 Replies
Highlighted
Contributor II

Re: Multiple currencies create circular reference

Try renaming the 'Currency Target' field in the CURRENCYRATES table to a new name such as 'Currency Target 2'

Highlighted
Not applicable

Re: Multiple currencies create circular reference

That doesn't work because it would defeat the purpose of having the filter for both tables

Highlighted
Partner
Partner

Re: Multiple currencies create circular reference

you have a circular loop because of multiple paths between tables.

Highlighted
Not applicable

Re: Multiple currencies create circular reference

Yes, exactly. but I need to have the same field in both tabels

Highlighted
Contributor III

Re: Multiple currencies create circular reference

Hi Francesco,

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

Highlighted
Contributor III

Re: Multiple currencies create circular reference

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 :

Sum({<Type={'Policy'}>} MeasureField*ExchangeRate)

The script :

// LOCATION :

LOCATION:

LOAD

  LocationID,

  locationPremium,

  'Curr|' & locationPremiumCurrency as LinkCurrencyRates,

  'Policy|' & PolicyID as LinkPolicyRates,

  locationPremiumCurrency&'|'&PolicyID as %ID_Fact_Bridge

From SourceLOCATION;

// Bridge

CurrencyBridge:

LOAD DISTINCT

  %ID_Fact_Bridge,

  LinkCurrencyRates as %Currency_Link,

  'Curr' as Type

From LOCATION;

concatenate(CurrencyBridge)

LOAD DISTINCT

  %ID_Fact_Bridge,

  LinkPolicyRates as %Currency_Link,

  'Policy' as Type

From LOCATION;

// Policy Rates

Rates:

LOAD

  policyPremiumCurrency,

  CurrencyTarget,

  PolicyExcRate as ExchangeRate

From SourcePolicyRates;

left join(Rates)

LOAD

  policyPremiumCurrency,

  'Policy|' & PolicyID as %Currency_Link,

  'Policy' as TypeRate

From SourcePOLICY;

Drop field policyPremiumCurrency;

// CurrencyRates

Concatenate(Rates)

  'Curr|' & locationPremiumCurrency as %Currency_Link,

  CurrencyTarget,

  ExchangeRate,

  'Curr' as TypeRate

From SourceCurrencyRates;

Highlighted
Not applicable

Re: Multiple currencies create circular reference

Hi Thomas,  thank you for your answer.

I tried your solution but it's not working.

is the Policy table missing some links?

in my data set, policy and location is not 1 to 1. a policy might have multiple locations.

Highlighted
Contributor III

Re: Multiple currencies create circular reference

Hi Francesco,

Oh, you mean that your measure field is PolicyPremium ?

Sum({<Type={'Policy'}>} PolicyPremium *ExchangeRate) ? If so I made a mistake.


Could you please show your measures ? Please, you need to better explain what you want.


BR,

Thomas Le Gall

Highlighted
Contributor III

Re: Multiple currencies create circular reference

Hello!

Try to:

qualify locationPremiumCurrency;

CURRENCYRATES:

LOAD

...

;


LOCATION:

LOAD

...

;


unqualify locationPremiumCurrency;