Loop in data structure with date/currency/XchRate

[BartVA]

I'm not sure how to avoid this data loop, any suggestions or tips would be really helpful. (I read some general explanations about data loops but not sure how to implement them in my model).

BankStatementsByWeek is the fact table, Journal is like a bank account number, and each has a currency. XchRate is a historical table with XchRates by currency and by date.

[Oleg_Troyansky · Accepted solution]

Hi Bart,

You need to do a bit of data modeling in order to resolve the loop, and it does need to be resolved, in order to produce expected results.

In your case, you are dealing with a slowly changing dimension - Exchange Rates. It's associated with Accounts based on Currency ID and it's associated with the "facts" by Date.

So, as a minimum, you could move the Currency ID field from the Journal table to the fact table, and then the loop will be resolved. You will still get a Synthetic key, that will include Date and CurrencyID. Ideally, you want to prevent it by building a combo key from these two key fields. However, if you decide to leave it, it's not as bad as the current loop.

Allow me to make an observation that based on several questions that I'm seeing from you, you could benefit greatly from taking a Qlik Sence development class, or from reading some Qlik books. Check out my book QlikView Your Business, for example. The tutorial is based on QlikView, however if you buckle up and struggle through it, you'd learn a great deal about Qlik application development, data modeling, scripting, etc...

Good luck!

[BartVA]

Yes, you're perfectly right of course, a good course on the fundamentals would not be a luxury. Thanks for the input and advice!