Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
TakacsZsolt
New Contributor II

Two Key Field more Tables without Circular Loop

Hi there,

I'm searching a solution to the problem below. I built a database, and now I need to link a plan table which contains plan values. How can I do this without "loop"? I would like to filter dates, tags etc. in Analysis after.

Important: one EXP ACC LINES ID can have multiple TAG ID.

exp_acc.png

 

Thanks in advance!

Labels (2)
5 Replies
MVP & Luminary
MVP & Luminary

Re: Two Key Field more Tables without Circular Loop

The easiest way is not to join and/or to associate both fact-tables else to concatenate (UNION in sql) them.

- Marcus

TakacsZsolt
New Contributor II

Re: Two Key Field more Tables without Circular Loop

Dear Marcus,

EXP ACC LINES has unique rows, but it may happen that one row has more TAG ID. If I use Concatenate unique rows will be multiplyed, and the consequence of this the values will be multiplyed too (SUM(NET VALUE) return 1000 instead of 500). Or am I wrong?

MVP & Luminary
MVP & Luminary

Re: Two Key Field more Tables without Circular Loop

No, that shouldn't happens - and without merging the measure-fields into a single field (requires an extra field which identified the measure-type respectively the data-source) the measures aren't touched in any way because for the net-data you used:

sum([NET VALUE])

and for the plan-data:

sum([PLAN VALUE])

- Marcus

TakacsZsolt
New Contributor II

Re: Two Key Field more Tables without Circular Loop

I did not mean that way, I don't want to join or concatenate the plan table, I just want to link it with two key field (DATE and EXP TYPE) to the right field and to the other tables without Circular Loop.

MVP & Luminary
MVP & Luminary

Re: Two Key Field more Tables without Circular Loop

By the simple association between two tables you could just create a composite key from your multiple key-fields. But if you have two fact-tables which needs to be connected to eachother and at the same time to further dimension-tables ... it's not possible in a classical way.

A common approach to handle such a scenario is the use of a link-table - but it needs some efforts to create it and from a UI performance point of view it's often not the best choice.

Therefore my suggestion to merge the fact-tables which could be done by various join/mapping measurements (which is usually quite complex but results in the smallest datamodel and best UI performance) - or much simpler by just concatenating the fact-tables. This approach might look "ugly" from a pure theoretical datamodeling point of view (especially with a sql-background) but it's very easy and the performance is usually more as sufficient.

Should you decide against a merging of tables and also against the link-table you need in each case to consider the possibility that there are current data which have no plan data or reverse - which is a quite common use-case and which couldn't be handled in any way with associating the tables.

- Marcus