How to avoid such loop?

[#1]

Hello guys,

I have a data set as illustrated in attached image. This data represents musical releases with related information. There is a dimension called Artists that must be linked to both Releases and Tracks tables (since one release can have multiple artists involved as well as track).

But as you can see from data structure I faced with a loop problem. The most suitable solution would be to use link table but I was unable to implement such pattern by myself.

Could someone give me a hint how to avoid such loop?

Thanks.

[srinivasa1]

Hi

Create diffrent cols useing as for %releaseId or.Use star schme data module.hope it will hlep you

[hic]

You need to remodel your data model.

I do not know the data, so I do not have the answer how. Is [%Release ID] uniquely identified by [%Track ID]? Then you do not need the [%Release ID] in the tracks table.

If the two fields are independent, I would create a table by joining "Track Artists" and "Release Artists". Then the Artists table would link to this table only, and this table would link further to the other tables through [%Release ID] or a combination of [%Track ID] and [%Release ID].

HIC

[#4]

Thanks for your reply Henric.

The thing is that each track is included into some release so Tracks table must have a reference to corresponding Release table.

Such structure as in my case is pretty common in relational model but does not suit well for assotiative one. I need to share somehow one dimension table Artists with two tables Releases and Tracks that are linked to each other. And this seems to be a problem.

[hic]

Do you need the "Release Artist" table at all? Aren't all artists in a release listed in the "Track Artist" table? If they are, you can just remove the "Release Artist" table.

If, on the other hand "Release Artist" and "Track Artist" are different lists of artists (so that one single release have different lists of artists in the two tables) then these are two different things and you will need to load the "Artist" table twice, once linking to "Track Artist" and once - with different field names - linking to "Release Artist".

HIC

[#6]

I will explain it using hands-on example to make it a bit clear. Let's suppose that we have Release called "The Best Club Tracks 2012" compiled by Release Artists DJ1 & DJ2. That Release contains tracks performed by different artists that are listed as Track Artists:

Track1:

     Band1

Track2:

     Singer1

     Band2

Track3:

     Singer2

     DJ1 (this one is one of this Release Artists - he just decided to include its own track into this release)

...

In another words some artists (usually DJs) can be both [Release Artist] and/or [Track Artist]. So both [Release Artists] and [Track Artists] should share the same dimension table [Artists].

I have already thought about having two instances of [Artists] table but this way is not optimal since [Artists] table has over 2.7M rows.

Using SQL we can specify which "route" to use to pull out information about artists - either through [Release Artists] or [Track Artists]. But in QlikView it is impossible as far as I know.

[hic]

Do the following:

1. Combine [Track artists] table with [Release Artists] table into one single table.
2. Add dummy records to the [Tracks] table.

HIC

[#8]

This approach will do the trick only if [%Track ID] and [%Release ID] fields do not share the same values. But it can be done by using AutoNumber() function with the same counter name for both fields. Am I right?

Another thing is that data structure becomes not so tidy.

Anyway, since I haven't got a better solution your one is really helpful. Thanks!