7 Replies Latest reply: Feb 5, 2013 1:33 PM by Yauheni Audzeichyk RSS

    How to avoid such loop?

    Yauheni Audzeichyk

      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).

       

      loop.PNG

      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.

        • Re: How to avoid such loop?
          Srinivasa D

          Hi

           

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

          • Re: How to avoid such loop?
            Henric Cronström

            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

              • Re: How to avoid such loop?
                Yauheni Audzeichyk

                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.

                  • Re: How to avoid such loop?
                    Henric Cronström

                    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

                      • Re: How to avoid such loop?
                        Yauheni Audzeichyk

                        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.