10 Replies Latest reply: Jun 15, 2011 9:51 AM by juliiian RSS

    Two fields in the same table linked with another table.

      Hi there,

       

      I was wondering how I could link two different fields of the same table to another table.

      In my case, I have two tables: The table User, and the table UserHierarchy.

       

      The table UserHierarchy contains the following fields: IdHierarchy, IdUser, IdUserChild. Both IdUser, IdUserChild refer to rows in the table "User".

      Now, I would like to display in a chart the list of employees of a user, but I can't come up with a way to achieve this.

       

      Could someone provide any help ?

      Thank you very much

       

      Julian

        • Two fields in the same table linked with another table.
          Anatoly Pyatygo

          Hello, Julian.

          if you no need a link by both fields (IdUser and IdUserChild) you can rename one field (I think it should be IdUserChild) in User table. after this you will get a link by one field - IdUser

            • Two fields in the same table linked with another table.
              Borja Rodriguez

              Julian,

               

              In this case you should make a new field wich would act as a composite key.

               

              It´s very easy, just have to concatenate both fields and name it "User_Hierarchy_key" for example:

               

              User:

              LOAD

                   IdUser,

                   IdUserChild,

                  IdUser & '^' & IdUserChild as User_Hierarchy_key,

              ....

               

              Create this field in both tables. Then, remove from one of the two tables the fields involving the composite key. For example, remove IdUser and IdUserChild from the User_Hierarchy table and keep them in the User table.

               

              If you have many records in some of the two tables involved, use de AUTONUMBER() function to build a more compact key (translates the concatenated string to a number, which is more memmory and calculations efficient).

               

              AUTONUMBER( IdUser & '^' & IdUserChild ) as User_Hierarchy_key,

               

              Hope this helps.

            • Re: Two fields in the same table linked with another table.

              Thank you for your answers. I still have a problem I don't manage to get ride of: I declare my table as follows:

               

              [User]:

              Load

                        Id as IdUser,

                        Id as IdUserChild,

                IdUser & '^' & IdUserChild as User_Hierarchy_key,

                        LastName,

                        FirstName,

                        Level

              //          WHERE Id = $(userid)

                        ;

              SQL SELECT

                        *

              FROM "*****".dbo."User";

               

              But it won't download my database this way, I get the following message:

               

              #####################

              Field not found - <IdUser>

              SQL SELECT

                        *

              FROM "******".dbo."User"

              #####################

               

              If I remove the line "IdUser & '^' & IdUserChild as User_Hierarchy_key,", my script works fine but QV creates a Syn table (is it the normal behaviour ? I've read that it's better to get ride of them)

               

              Thank you once again,

              Julian

                • Re: Two fields in the same table linked with another table.

                  I’m not sure to understand your problem, but I hope this will be helpful :

                  For the “field not found”, if you rename a field in a load statement you cannot use it in the same load statement, you have to do something like :

                   

                  load

                    Id as IdUser,

                    Id as IdUserChild,

                    IdUser & '^' & IdUserChild as User_Hierarchy_key;

                  load

                    Id as IdUser,

                    Id as IdUserChild;

                  sql select...

                   

                   

                  For theSync table it’s the normal behavior, Qlikview link fields on their names and can do a link only on one field, so if two tables have more than one common field you will have a sync table.

                   

                  If you have only two levels of users the simplest way is to create a table for managers and another one for employee :

                   

                  tmpUser:

                  load

                    Id as IdUser,

                    Name

                  sql select...

                   

                  Hierarchy:

                  load

                    IdHierarchy,

                    IdUser,

                    IdUserChild

                  sql select...

                   

                  User:

                  load distinct

                    IdUser

                  resident Hierarchy;

                   

                  left join load

                    IdUser;

                    Name

                  resident tmpUser;

                   

                  UserChild:

                  load

                    IdUserChild

                  resident Hierarchy;

                   

                  left join load

                    IdUser as IdUserChild;

                    Name

                  resident tmpUser;

                   

                  drop table tmpUser;

                • Two fields in the same table linked with another table.

                  Thank you for your answer. I will try the code you provided.

                   

                  Unfortunately, I have 3 levels of users, and we will probably create other levels later

                  • Re: Two fields in the same table linked with another table.

                    Thanks everybody, I denormalized this part of my db and created a different dimension, it did the trick!

                    I still have a problem, maybe someone 'll be able to help me. In a chart, I have the following expression (it's a dimension) : =if(IdUser=userid, IdUserChild)

                     

                    The other columns display data about the User (because I used the IdUser field, part of the User table), and not about his child (which is also a User, but I would like QV to use the IdUserChild (FK attached to the User table) field as the identifier to be used, and not IdUser).