topic Re: How to solve a circular reference but not lose data comparisonability in App Development

How to solve a circular reference but not lose data comparisonability

Josecanalla1 — Mon, 26 Dec 2022 21:01:58 GMT

Hello, I have the next Data Model:

Of course it is simplified, it contains more fields and more tables.

I am having a circular reference error because of the date through the MasterCalendar.

I am developing a functionality that allows the user to see the budget amount per month and what amount is already paid on each category. Categories are the units in which I want to group payments and budgets. For example I would have a category "Food" and then I would have a BudgetLine which says that I have $500 for that category and then I'll have several InvoiceLines pointing to products of that Category which says that I've already spent $200 in "Food".

For that reason for me it's extremely important that the two tables (payments and budget) share the same MasterCalendar. But I obviously need to solve the circular reference...

So in what way do you think that I could solve this issue? Thanks!

Re: How to solve a circular reference but not lose data comparisonability

vinieme12 — Tue, 03 Jan 2023 07:54:38 GMT

Consolidate your data model as below, i've indicated the transformation process and the final datamodel

I've assumed cardinality as it's not mentioned in your snapshot

Re: How to solve a circular reference but not lose data comparisonability

Josecanalla1 — Wed, 04 Jan 2023 11:15:04 GMT

Hi, thanks for your reply.

Could you explain me with an example how to make the left joins? I'm not understanding how to make a left join to the same table.

Re: How to solve a circular reference but not lose data comparisonability

vinieme12 — Wed, 04 Jan 2023 11:21:37 GMT

Hi, please spend some time on learning and research

There is plenty of content available on Qlik YouTube channel, Free Qlik training videos on YouTube as well as materials on QlikHelp and community among other channels

Re: How to solve a circular reference but not lose data comparisonability

Josecanalla1 — Wed, 04 Jan 2023 11:52:39 GMT

Sorry, I appreciate your time but I made a lot of research before asking. This is a forum, so if you suggest me a solution I think that you should explain me in what way it would work.

I don't understand what you mean when you say for example

FACT

Load InvoiceLine

Left Join (FACT)

If you could, it would be great to explain me what is that.

Thanks