9 Replies Latest reply: Feb 28, 2017 10:02 AM by Andrey Khoronenko RSS

    Relationships between key values ($Syn Tables and Circular References)

    Joanna Waligora

      Hi,

       

      SCENARIO

       

      I have a table with animal details:

       

      Animals:

      LOAD Animal,

           ID,

           Age,

           Sex

      FROM

      [.\Animals.xlsx]

      (ooxml, embedded labels, table is Sheet1);

       

      The animals include Tigers and Bunnies. Due to a security breach, some of the tigers got into the Bunny enclosure and ate a few of the Bunnies.

       

      I have a second table that outlines who ate who;

       

      Ate:

      LOAD Tiger_ID,

                Bunny_ID

      FROM

      [.\Animals_eat.xlsx]

      (ooxml, embedded labels, table is Sheet1);

       

      Both Tiger_ID and Bunny_ID belong to the set of IDs present in the Animal table.

       

      QUESTION

       

      I would like to create an association such that selecting an ID which belongs to a culprit tiger would display both the details about said tiger as well as the bunnies it consumed. Conversely, selecting an ID of a consumed bunny, would show information about the bunny as well as its murderer.

       

      What is the recommended approach to apply in the data loading script?

      Do I need to break up the Animal table, such that Tigers have one table and Bunnies have another? That would not be ideal for all my other charts/metrics, but trying to make it work via other means has thus far yielded no positive results.

       

      Thanks,
      J.

        • Re: Relationships between key values ($Syn Tables and Circular References)
          Anil Babu Samineni

          How is your data model looks

           

          I would request you to create two excels and then share and tell us with your expected out put?

           

          Even we can follow this approach to prove. Or we can create single file

           

          Animals:

          LOAD RowNo() as RowNo,Animal,

               ID,

               Age,

               Sex

          FROM

          [.\Animals.xlsx]

          (ooxml, embedded labels, table is Sheet1);

           

          Ate:

          LOAD RowNo() as RowNo,Tiger_ID,

                    Bunny_ID

          FROM

          [.\Animals_eat.xlsx]

          (ooxml, embedded labels, table is Sheet1);

           

          This will prove associate for your model

           

          Or

           

          Animals:

          Mapping LOAD Animal,

               ID,

               Age,

               Sex

          FROM

          [.\Animals.xlsx]

          (ooxml, embedded labels, table is Sheet1);

           

          Ate:

          LOAD Tiger_ID,

                    Bunny_ID

          FROM

          [.\Animals_eat.xlsx]

          (ooxml, embedded labels, table is Sheet1);

           

          Left Join(Ate)

          Load Tiger_ID, Bunny_ID, ApplyMap('Animals',Animal) as Animal, ID, Age, Sex

          Resident Animals;

          Drop Table Ate;

          • Re: Relationships between key values ($Syn Tables and Circular References)
            Andrey Khoronenko

            Hi Joanna,

             

            Maybe so.  When loading table Ate to create new field [KEY} with composite key, format such as  'Tiger_ID'&'Bunny_ID'. Then when loading table Animals to create a similar field [KEY}. Each records of table Animals check if the coincidence of the values of fields ID and  fields Tiger_ID or Bunny_ID in the table Ate, in field [Key] table Animals recorded the corresponding key 'Tiger_ID'&'Bunny_ID' format.

            Thus both tables will be contacted as a result of a composite key.

             

            This is an example script code

             

            //create table Ate

            Ate:

            LOAD

            RecNo() as Tiger_ID,

            RecNo()*10 + 41 as Bunny_ID

            AutoGenerate 5;

             

            //create a composite key for table Ate

            Left Join

            LOAD*,

            Tiger_ID&'|'&Bunny_ID as Key

            Resident Ate;

             

            //create temp Key Table

            NoConcatenate

            TableKey:

            LOAD

            If(IterNo()=1, Tiger_ID, Bunny_ID) as ID_Key,

            Key

            Resident Ate

            While IterNo()<=2;

             

            //create table Animals

            Animals:

            LOAD If(RecNo()<=50, 'Tiger'&RecNo(), 'Bunny'&RecNo()) as Animal,

                 RecNo() as ID,

                 Div(Rand()*10, 1) as Age,

                 If(Rand()>0.5, 'F', 'M') as Sex

            AutoGenerate 100;

             

            //create a composite key for table Animals

            Left Join(Animals)

            LOAD

            ID_Key as ID,

            Key

            Resident TableKey;

             

             

            DROP Table TableKey;

             

            Result

            1.jpg

            The data generated in the script automatically, so you can just copy the code at QlikView or Qlik Sense

             

            Regards,

            Andrey

            • Re: Relationships between key values ($Syn Tables and Circular References)
              Joanna Waligora

              Hi loveisfail, ahaahaaha!

               

              Thanks so much for your suggestions. Sorry for not posting the source data. I'm adding it now for reference. Part of the problem is that it could be a one-to-many mapping, and quite possibly even a many-to-many mapping (it was a bloodbath). I opted for a somewhat simplistic solution of dumping everything into a single table. It isn't exactly the level of automation I hoped for, but I can work around it (and chances are what I had in mind would by definition generate a circular reference). I have two toggle buttons 'I ate it.' and 'It ate me.' that filter out each type of data.

               

              ...Andrey, your implementation is an excellent, concise study of AutoGenerate, IterNo(), RecNo() and a few other QV concepts. I'll be starring at it for a while.

               

              Animals:

              LOAD Animal,

                   ID,

                   Age,

                   Sex

              FROM

              [.\Animals.xlsx]

              (ooxml, embedded labels, table is Sheet1);

               

              left join

              LOAD Tiger_ID as ID,

                   Bunny_ID

              FROM

              [.\Animals_eat.xlsx]

              (ooxml, embedded labels, table is Sheet1);

               

              left join

              LOAD Bunny_ID as ID,

                   Tiger_ID

              FROM

              [.\Animals_eat.xlsx]

              (ooxml, embedded labels, table is Sheet1);

                • Re: Relationships between key values ($Syn Tables and Circular References)
                  Andrey Khoronenko

                  Hi Joanna,


                  We take into account your remark about the a many-to-many mapping to create a composite key.

                   

                  In this case, the script code for your data may be as follows.

                   

                  //load Animals_eat

                  Directory;

                  TableAnimals_eat:

                  LOAD Tiger_ID,

                      Bunny_ID

                  FROM

                  Animals_eat.xlsx

                  (ooxml, embedded labels, table is Sheet1);

                   

                  //create composite key

                  Left Join

                  LOAD

                  Tiger_ID,

                  Tiger_ID&'|'&Concat(Bunny_ID, '|') as KeyEat

                  Resident TableAnimals_eat

                  Group By Tiger_ID;

                   

                  //create temp Key Table

                  NoConcatenate

                  TableKey:

                  LOAD Distinct

                  If(IterNo()=1, Tiger_ID, Bunny_ID) as ID_Key,

                  KeyEat

                  Resident TableAnimals_eat

                  While IterNo()<=2;

                   

                  //load Animals

                  Animals:

                  Directory;

                  LOAD Animal,

                      ID,

                      Age,

                      Sex

                  FROM

                  Animals.xlsx

                  (ooxml, embedded labels, table is Sheet1);

                   

                  //create a composite key for table Animals

                  Left Join(Animals)

                  LOAD

                  ID_Key as ID,

                  KeyEat

                  Resident TableKey;

                   

                  DROP Table TableKey;

                   

                  Result

                   

                  2.jpg

                  3.jpg

                   

                  I hope this is what you need.

                   

                  Regards,

                  Andrey

                    • Re: Relationships between key values ($Syn Tables and Circular References)
                      Andrey Khoronenko

                      The above example shows in fact one-to-many mapping (one Tiger eats one or some Bunnys). If some Tigers eats some Bunnys, there is many-to-many mapping. In this case, it seems to me, must be new field in the table Animals_eat.xlsx, which contains a unique value for each case of eating (for example field "Case" in the attached file xlsx).

                      The value of this new field will be included in a composite key when linking tables.

                       

                      Regards,

                      Andrey

                        • Re: Relationships between key values ($Syn Tables and Circular References)
                          Joanna Waligora

                          Yes, my example was simplistic. When I alluded many-to-many, what I had in mind is two or more tigers sharing the same bunny, rather than a case classification of consumption instances. Like so:

                           

                          Tiger_ID     Bunny_ID

                          T03            B01

                          T03            B02

                          T03            B04 B10

                          T05            B10

                           

                          Thanks,
                          J.

                            • Re: Relationships between key values ($Syn Tables and Circular References)
                              Andrey Khoronenko

                              Yes, I have already analyzed this situation. Source file

                               

                              1.jpg

                               

                              A working code for this event next

                               

                              //load Animals_eat

                              Directory;

                              TableAnimals_eat:

                              LOAD Tiger_ID,

                                   Bunny_ID

                              FROM

                              Animals_eat.xlsx

                              (ooxml, embedded labels, table is Sheet1);

                               

                               

                              //create composite key whom ate

                              Left Join(TableAnimals_eat)

                              KeyEatWhom:

                              LOAD

                              Tiger_ID,

                              Concat(Bunny_ID, '|') as KeyEatWhom

                              Resident TableAnimals_eat

                              Group By Tiger_ID;

                               

                               

                              //create composite key who ate

                              Left Join(TableAnimals_eat)

                              KeyEatWho:

                              LOAD

                              Bunny_ID,

                              Concat(Tiger_ID, '|') as KeyEatWho

                              Resident TableAnimals_eat

                              Group By Bunny_ID;

                               

                               

                              //create composite common key

                              Left Join(TableAnimals_eat)

                              LOAD*,

                              KeyEatWho&'|'&KeyEatWhom as KeyEatCommon

                              Resident TableAnimals_eat;

                               

                               

                              DROP Fields KeyEatWho, KeyEatWhom From TableAnimals_eat;

                               

                               

                              //create temp Key Table

                              NoConcatenate

                              TableKey:

                              LOAD Distinct

                              If(IterNo()=1, Tiger_ID, Bunny_ID) as ID_Key,

                              KeyEatCommon

                              Resident TableAnimals_eat

                              While IterNo()<=2;

                               

                               

                              //load Animals

                              Animals:

                              Directory;

                              LOAD Animal,

                                   ID,

                                   Age,

                                   Sex

                              FROM

                              Animals.xlsx

                              (ooxml, embedded labels, table is Sheet1);

                               

                               

                              //create a composite key for table Animals

                              Left Join(Animals)

                              LOAD

                              ID_Key as ID,

                              KeyEatCommon

                              Resident TableKey;

                               

                               

                              DROP Table TableKey;

                               

                              Result

                              2.jpg

                               

                              3.jpg

                               

                              However, if out of the source file to delete the last line, we get the result

                               

                              4.jpg

                              5.jpg

                               

                              Look carefully at the resulting synthetic keys. In the latter case we have "a porridge".

                              Brief conclusions:

                              1. With many-to-many mapping quality of the analysis depends on the quality of the source data. And since they will all be entered manually, mistakes are possible. Errors will be in the analysis.

                              2. If you ignore the many-to-many mapping eating each case is unique (I wrote above about it), you can get a data structure unsuitable for analysis.

                              3. These considerations are valid for both discussed ways to build data, as for the left join tables in your proposal and for my proposal to use synthetic keys.

                               

                              Regards,

                              Andrey

                                • Re: Relationships between key values ($Syn Tables and Circular References)
                                  Joanna Waligora

                                  Hi Andrey,

                                   

                                  I'm attaching a version with my 'big table' implementation. The first tab 'Main_view' demonstrates what associative behaviour I'm after. (Only the variable value picker and the two tables below it are of relevance; the ones on the side are just for reference.)

                                   

                                  Your implementation sits in its own script tab named 'Andrey'. Promoting it to 1st script tab will run it instead of my 'Main' code. On the second page of the layout, labelled 'Andrey_view' is a duplicate of the 'Main_view' layout. I can't figure out how to define the expressions in the 'Variable Selection Output' table and the 'Reverse Lookup' table, to obtain the same result using  the 'Andrey' load as using the 'Main' load. Would it require a different set of keys?

                                   

                                  In case I over-confused the matter (which I know I did), I'm ready to apologize with some muffins, or given your name, possibly a bottle of vodka.

                                   

                                  Thanks,

                                  J